Delete leading text in text file

[Roli001]

Hi,

I have a large text file (80 MB) that is downloaded daily. The file has leading text with the source code of the file (about 60 characters).

I have the text file linked to Access, however before I run my queries, I have to open the text file in Notepad and delete the leading text from the file until the words "Item Number,".

I found this code from WayneRyan however it does not work as the file is too big:

Dim buffer As String

Open Me.MyFile For Input As #1
Open "C:\Temp.txt" For Output As #2

Line Input #1, buffer
While Not EOF(1) And InStr(1, buffer, "TypeRI") = 0
   If EOF(1) Then
      MsgBox("Invalid File.")
      Close #1
      Close #2
      Exit Sub
   End If
   Line Input #1, buffer
   Wend

While Not EOF(1)
   Print #2, buffer
   Line Input #1, buffer
   Wend

Close #1
Close #2

Kill Me.MyFile
Name "C:\Temp.txt" As Me.MyFile

MsgBox("Done.")

I appreciate any help you can give.

Roli001

 

[CyberLynx]

A 80MB Text File? As in 80 Mega-Byte?

Is that a typo? With the code you have posted, it should work. It's just that it would take about 15 to 20 minutes to process (I should think).

.

 

[DCrake]

Is it possible to post the first few rows of the text file for testing purposes.

 

[Roli001]

A 80MB Text File? As in 80 Mega-Byte?

Is that a typo? With the code you have posted, it should work. It's just that it would take about 15 to 20 minutes to process (I should think).

.

It takes me 15 - 20 seconds to open in Notepad, delete the leading text and save the file.

 

[Roli001]

Is it possible to post the first few rows of the text file for testing purposes.

dailydump testhttp://website/replenishment/daily file/item file/20081027 <> test file <test>ITEM NUMBER,FACILITY,ITEM DESCRIPTION,ITEM SIZE QTY,ITEM SIZE UNIT,VENDOR NO,VENDOR DIVISION,VENDOR NAME,PICKUP POINT INFO,DC ITEM TIE,DC ITEM TIER,DC ITEM WEIGHT,DC ITEM CUBE,VENDOR TIE,VENDOR TIER,VENDOR WEIGHT,VENDOR CUBE,ORDER RESTRICTION,ORDER UNIT,MIN QTY,MIN TYPE,MAX QTY,MAX TYPE,BUYER NUMBER,CODE,LEAD TIME STATED,LEAD TIME AVE,MASTER ITEM CASE SIZE,SHIP ITEM CASE SIZE,REVIEW DAY 1,REVIEW DAY 2,REVIEW DAY 3,REVIEW DAY 4,REVIEW DAY 5,REVIEW DAY 6,REVIEW DAY 7,IN-OUT INDICATOR,ACTIVE INDICATOR,TEMP OUT OF STOCK,SHELF LIFE,ITEM LIST DATE,ITEM PURCHASING STATUS,ITEM BILLING STATUS,WAC,FIRST COST,REGULAR ON-HAND,FIRM PROMO ON-HAND,ESTIMATE PROMO ON-HAND,REGULAR ON-ORDER,FIRM PROMO ON-ORDER,ESTIMATE PROMO ON-ORDER,SHIPMENTS WK1,SHORTS WK1,SHIPMENTS WK2,SHORTS WK2,SHIPMENTS WK3,SHORTS WK3,SHIPMENTS WK4,SHORTS WK4,SHIPMENTS WK5,SHORTS WK5,SHIPMENTS WK6,SHORTS WK6,SHIPMENTS WK7,SHORTS WK7,SHIPMENTS WK8,SHORTS WK8,SHIPMENTS WK9,SHORTS WK9,SHIPMENTS WK10,SHORTS WK10,SHIPMENTS WK11,SHORTS WK11,SHIPMENTS WK12,SHORTS WK12,SHIPMENTS WK13,SHORTS WK13,CUR WK SHIPMENTS,CUR WK SHORTS,RUN-DATE,
100024,72,Item 2 , 1 ,EA ,11770,O,Vendor 1.5 ,BRANTFORD ON N3V1G1,003,002,000004.08,0000.175,001,001,000004.08,0000.175,U,C,0018100,100 KG ,0000060,PALLETS,237,51045010,005,008,00036,00036,Y,Y,Y,Y,N,N,N,Y,N,N,182,20070228,S,A,15,00200.8400,0000000,0000000,0000000,0000000,0000000,0000000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,00000,20081018,
100056,03,Item1 , 1.50,KG ,07994,B,Vendor 1 INC. ,Nowhere QC H4P2K9,012,003,000009.57,0000.015,012,004,000009.57,0000.015,W,C,0022000,100 KG ,0028800,100 KG ,204,90423010,004,012,00006,00006,Y,Y,Y,Y,Y,N,N,N,Y,N,150,20010614,A,A,20,00136.4200,0000009,0000000,0000000,0000036,0000000,0000000,00019,00000,00019,00000,00017,00000,00018,00000,00058,00000,00001,00031,00024,00005,00016,00000,00015,00000,00020,00000,00013,00000,00014,00000,00018,00000,00000,00000,20081018,

This is the first 2 rows + the leading information. I need to delete everything up to "Item Number" or have access import from the words "Item Number" with the first row as the column descriptions.

 

[DCrake]

Use the open file for input as normal when reading the source file for the first time and use the Instr(StrLine,"ITEM NUMBER") to detect where the first column heading is.

Then use StrLine = Left(StrLine,P-1) (Where P is the InStr Position

Then open up #2 and write the line to the new file and loop though until EOF

Close #1,#2
Open up #2 and see if it has worked

You may need to create an import spec as my test created three import errors

 

[DCrake]

Just had another thought

If you have a static text file on your pc that just contains the field names (first row in file) Then each time you want to run the import you copy this file to a new name, then when you open the import file start the append from row 2 (first line that contains data.)

So
FileCopy (Headings.txt,ImportDump.txt)

Open File NewImport.txt For Input As #1
Open File ImportDump For Append As #2
Dim nRow As Integer
nRow = 1
While EOF
If nRow > 1 then
Line Input #1 , StrLine 'Read in the data items
Print #2, StrLine ' Write the data out to import dump file
End If
nRow = nRow +1
Wend
Close #1
Close #2

This will mean you do not have to worry about stripping off the leading text each time you open the import file.

 

[Roli001]

So I figured out how to overcome the issue. Some solutions are so simple that it is impossible to see them until they hit you in the face.

I used the import wizard and simply changed column 1 heading name to "Item Number". Because this is a "," delimited file the leading text and the words "Item Number" fell under the first column so it was just a matter of renaming it.

This still doesn't solve the "complicated" solution that I was looking for, namely deleting the first n number characters without having to loop through the entire file and saving a duplicate with all information from character n until EOF.

Thanks all for your support!

 

[DCrake]

Please find attached demo of how to import your data directly into an existing table using an import specification.

I have created a single table (Test)

2 Macro
2 converted macros

1 Import Specification

2 Text files which need to be in C:\Temp
a) C:\Temp\Test.Txt
b) C:\Temp\New Test.Txt


a) Does not contain the leading text
b) Does contain the leading text

Try running both macros they both work ok


Caution:
After you have run any of them check the contents of the fields you will not that some fields that I suspect should be numeric actually have spaces between the digits. This may need to be addressed with the people who create the file for you.

David

 

[Roli001]

Please find attached demo of how to import your data directly into an existing table using an import specification.

I have created a single table (Test)

2 Macro
2 converted macros

1 Import Specification

2 Text files which need to be in C:\Temp
a) C:\Temp\Test.Txt
b) C:\Temp\New Test.Txt


a) Does not contain the leading text
b) Does contain the leading text

Try running both macros they both work ok


Caution:
After you have run any of them check the contents of the fields you will not that some fields that I suspect should be numeric actually have spaces between the digits. This may need to be addressed with the people who create the file for you.

David

I downloaded the files and put them in C:\Temp however when I try to open the mdb file, I get an error report:

This file is located outside your intranet or on an untrusted site. Microsoft Access will not open the file due to potential security problems.

To open the file, copy it to your machine or an accessible network location.