Import part of txt table to DB

[luismagally]

I have a file exported from Quickbooks which I need to import to a table. The problem is that I need to delete or bypass the first column of the file as well as a variable number of rows in the file. From then I need to read the data by chunks into a table. Here is a sample of the data:

!TIMERHDR VER REL COMPANYNAME IMPORTEDBEFORE FROMTIMER COMPANYCREATETIME
TIMERHDR 6 0 XXXXX XXXXXXXXXXX Group, PSC N Y 1087415074
!CUST NAME REFNUM TIMESTAMP BADDR1 BADDR2 BADDR3 BADDR4
BADDR5 SADDR1 SADDR2 SADDR3 SADDR4 SADDR5 PHONE1 PHONE2 FAXNUM
CONT1 CONT2 CTYPE TERMS TAXABLE LIMIT RESALENUM REP
TAXITEM NOTEPAD SALUTATION COMPANYNAME FIRSTNAME MIDINIT
LASTNAME CUSTFLD1 CUSTFLD2 CUSTFLD3 CUSTFLD4
CUSTFLD5 CUSTFLD6 CUSTFLD7 CUSTFLD8 CUSTFLD9
CUSTFLD10 CUSTFLD11 CUSTFLD12 CUSTFLD13 CUSTFLD14
CUSTFLD15 JOBDESC JOBTYPE JOBSTATUS JOBSTART JOBPROJEND
JOBEND HIDDEN DELCOUNT
CUST A E G Administration Project 400 1087831556 0 0
!EMP NAME REFNUM TIMESTAMP INIT ADDR1 ADDR2 ADDR3
ADDR4 ADDR5 SSNO PHONE1 PHONE2 NOTEPAD FIRSTNAME MIDINIT
LASTNAME SALUTATION CUSTFLD1 CUSTFLD2 CUSTFLD3
CUSTFLD4 CUSTFLD5 CUSTFLD6 CUSTFLD7 CUSTFLD8
CUSTFLD9 CUSTFLD10 CUSTFLD11 CUSTFLD12 CUSTFLD13
CUSTFLD14 CUSTFLD15 HIDDEN DELCOUNT
EMP Chooro Ckhjdo, Jkhjk 13 1087415432 Joel Castro Castro Mr. 0
!CLASS NAME REFNUM TIMESTAMP HIDDEN DELCOUNT
CLASS Engineering 2 1087416444 0
!INVITEM NAME REFNUM TIMESTAMP INVITEMTYPE DESC
PURCHASEDESC ACCNT ASSETACCNT COGSACCNT QNTY QNTY
PRICE COST TAXABLE PAYMETH TAXVEND TAXDIST PREFVEND
REORDERPOINT EXTRA CUSTFLD1 CUSTFLD2 CUSTFLD3
CUSTFLD4 CUSTFLD5 DEP_TYPE ISPASSEDTHRU HIDDEN
DELCOUNT USEID
INVITEM Engineering 8 1087416454 SERV 0
INVITEM Engineering:Engineering Unbillable Time 11 1087416455 SERV 0
INVITEM Engineering:Eng. CAD Work 9 1087416455 SERV 0
!TIMEACT DATE JOB EMP ITEM PITEM DURATION
PROJ NOTE XFERTOPAYROLL BILLINGSTATUS
(delete of disregard all of the above)
TIMEACT(need to read from this point)
12/20/04 A E G Administration Project Castro Castro, Joel
Engineering:Engineering Unbillable Time 03:05 Engineering
N 1
TIMEACT 12/20/04 Warner Chilcott:OC's Area Solid Processing Module
Castro Castro, Joel Engineering:Eng. CAD Work 03:19
Engineering N 1
TIMEACT 12/20/04 Paredes Luciano Architects:New Showroom Facilities
Garaje Isla Verde Castro Castro, Joel Engineering:Eng. CAD Work
01:11 Engineering N 1
TIMEACT 12/27/04 Warner Chilcott:OC's Area Solid Processing Module
Castro Castro, Joel Engineering:Eng. CAD Work 01:50
Engineering N 1
TIMEACT 12/27/04 Parsons:Wastewater Treatment Plant Castro
Castro, Joel Engineering:Eng. CAD Work 02:04 Engineering
N 1
(stop reading)
;__IMPORTED__

I don't seem to find a good solution yet to this problem, since the field length in most cases changes with the information. I could reformat some fields to give specific size if necessary.

Anyone got a suggestion?

 

[DrSimon]

I would do this by using VBA code to read each line and then inspect the first N characters to determine whether or not to accept the data. You would then need to parse the acceptable data and write to the table. I don't think there's any other way of dealing with this.
Simon Rouse

 

[Zion7]

agreeing with Simon, unless there
are any constants to use for search criteria.

at a quick glance, TIMEACT is where you start reading?

:__IMPORTED__ is where you stop. is that the general rule?

 

[luismagally]

Thanks for the reply,

TIMEACT is the start, but I need only specific fields, like the date, nae, etc. ;__IMPORTED__ is where I would stop to read.

Like this:

TIMEACT 12/20/04 Warner Chilcott:OC's Area Solid Processing Module
Castro Castro, Joel Engineering:Eng. CAD Work 03:19
Engineering N 1


Those are the fields that I need. If so, do you have any suggestions about the code to use to read the file? I have not work with external files and I don't know much about the code to use, or at least an example to work with.

Thanks for your help.

 

[Zion7]

look up , FileSystemObject in VBA help.

Amongst several of the methods, that you will need,

There's a ReadAll Method, where you can use the InStr() function.

Good Luck!

 

[DrSimon]

Zion7 and I are both thinking on the same lines and his advice is correct. However looking at your examples I can't see any easy way to piece out the data (find the columns) using InStr() or any other way. If the spaces represent Tab or some other invisible separator, you need to use the Split() function, which I find invaluable in these circumstances.
Simon Rouse

 

[Zion7]

Thank-you Simon, I would agree, Split() much more appropriate in this circumstance!

 

[CautionMP]

I'm a little late to the ballgame, but here are a couple of thoughts.

I'm guessing that the source file you provided got manipulated when you pasted it. If you look at the file in it's native form I would guess that the column/record structure is more apparent.

Have you tired the text import wizard? You may be able to set the parameters for the section of 'data' you are looking to capture and then use an append query to move the data. When you run the wizard the first section of information (that you don't want) will look like junk data, but that's ok since you don't want to capture it, just make sure that when you get to the data you want that TIMEACT is the first column of each row.

A quick way to test if this will work is to change your Quickbooks data file extension to .csv then open the file with MS Excel and see if you can get the file into the appropriate rows/columns.

Hope this helps,
B.