prob with importing csv

[sal21]

I have a csv file with many fields.
The file is created from a CICS procedure.
to export the csv file a need to insert a step of date, generally from moonday and friday.
The step in this case is 5 days
when the file is created (for example i insert 07/06/2010 11/06/2010 ) the head of fileds are 07/06/2010,08/06/2010 ,09/06/2010 ,10/06/2010 and 11/06/2010.
But if in the step of date aree a holyday day (for example 08/06/2010) the CICS program create a csv with 07/06/2010,09/06/2010 ,10/06/2010 and 11/06/2010.
i need to import always the step 07/06/2010,08/06/2010 ,09/06/2010 ,10/06/2010 and 11/06/2010.(in this case the column 2day have a blank value
In this case i use a sheet in excel to import the file and have in a column 1day,2day,3day,4day,5day
The file is delimited from tab and i use a vba code to import.
How to fill the column ???
i hope undesrtand me!

[HansV]

Do you already have some code or are you starting from scratch?

[sal21]

Do you already have some code or are you starting from scratch?

attached 2 files
the file rpt_day_++++_20100531_20100604.TXT have 4 days (02/06/2010 is a holiday in Italy)
the file rpt_day_++++_20100607_20100611.TXT is normal with 5 days

here the code i sue:

Code: Select all

Sub uno()

    Dim strinput As String
    Dim inputstr As String
    Dim outputstr() As String
    Dim X As Long

    Open "c:\temp\rpt_day_++++_20100531_20100604.TXT" For Input As #1

    While Not EOF(1)
        Line Input #1, strinput
        outputstr = Split(strinput, vbTab)
        For X = 0 To UBound(outputstr)
            Debug.Print outputstr(X)
        Next
    Wend
    Close 1

End Sub

note:
omitted entire name of file for sensible data

[HansV]

I'd open the text files in Excel using Workbooks.OpenText, but they appear to be in different formats. Excel complains that one of them is actually a SYLK file.

When you open them in Excel, you can easily inspect the header row and copy the correct columns.

x217.png

[sal21]

I'd open the text files in Excel using Workbooks.OpenText, but they appear to be in different formats. Excel complains that one of them is actually a SYLK file.

When you open them in Excel, you can easily inspect the header row and copy the correct columns.

x217.png

i' m sorry change the extention of file from .txt to .xls

[HansV]

That won't work, it is a text file. It appears to be a bug in Excel 2007. Can you open rpt_day_8501_20100531_20100604.txt in your version of Excel?

[sal21]

That won't work, it is a text file. It appears to be a bug in Excel 2007. Can you open rpt_day_8501_20100531_20100604.txt in your version of Excel?

yes i can read and import in my excel...

[HansV]

Good! If you open the text file in Excel, you can easily copy the data that you need into the target workbook.

[sal21]

Good! If you open the text file in Excel, you can easily copy the data that you need into the target workbook.

ok... but how to check if one or more date column are missing based the 5 dates in standard file?
If you see into the post Post=21391 you have attached the result into a table...
based the step of dates into the the file rpt_day_8501_20100531_20100604.txt is missing the date 02/06/2010... in this case i nedd to fill thsi date to 0 (zero)

[HansV]

You can loop through the columns and check whether there is a missing date.