Import CSV file into table omitting first 4 rows 1

[jeffwest2]

I need to create an import routine for a csv file into a table.

The file contains 4 rows of junk at the start of the file, which while i can manually remove prior to the load, I want to have as little user inter action with the file, Can anyone suggest a very quick piece of VBA that will start the import from row 5 which is the header row.

Regards

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[jeffwest2]

Sorry, just realised this also has a footer which will be at a non static row in the file, but will be preceeded by a space row, so need to get rid of that as well :-(

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[randy700]

One method:

Import the file into a temp table, with an autonumber (key) field.
Determine the lowest and highest number in the key field.
Using those values, run an append query to move the data to the proper table.
Delete everything from the temp table so you're ready for the next time.


Randy

 

[Andrzejek]

Another method,

Open your text file
Read it line-by-line
After skipping 4 lines, at line 5 write the record to another text file (if it is NOT blank)
If you have blank line – the footer – you are done writing
Point your import routine to the newly created text file


Have fun.

---- Andy

 

[jeffwest2]

How would I open the file and read it past line 4? Never done that before so a little unsure how that would work.



Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[Andrzejek]

Something like:

Dim strTextLine As String
Dim i As Integer

Open "C:\TestFolder\textfile.txt" For Input As #1
Open "C:\TestFolder\New_textfile.txt" For Output As #2

Do While Not EOF(1)             [green]' Loop until end of file.[/green]
   i = i + 1
   If i > 4 Then
      Line Input #1, strTextLine   [green]' Read line into variable.[/green]
      If Len(Trim(strTextLine)) > 0 Then[green]
         'Write into a new text file[/green]
         Print #2, strTextLine
      End If
   End If
Loop
Close #1
Close #2

Have fun.

---- Andy

 

[jeffwest2]

Cheers Andrzejek, that worked a treat, much appreciated.

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.

 

[Andrzejek]

Glad to help

Have fun.

---- Andy