How do I i create a new table of data with set fields from an imported text file that is just a string of text.. (1 Viewer)

[Number11]

So i need to break up a report that is provided in a continued string of data..

A1215455545444444842121212121212121TTTRTR2TR2TY2212121212212122121
The data will need to be broken out as following..

Record type	String	1	Always "A"
Supplier ID	Numeric	5	XXX01 where XXX is the 3-digit Supplier ID
Line Number	String	20
Product Number	String	12
Pick Location Number	String	12
Load Date	Numeric	8
Load Time	Numeric	6
Unit Cost Amount	Numeric	6	££££pp

not sure if its possible?

 

[Gasman]

Use the String functions?

 

[GPGeorge]

You'll need the Left() and Mid() functions to retrieve the various elements shown.

strRecordType= Left([YourfieldNameGoesHere], 1)
lngSupplierID = Mid([YourfieldNameGoesHere], 2,5)

and so on.

Once you've parsed the string from each record in the input text, you can use the variables to insert the values into the appropriate table.

 

[Number11]

You'll need the Left() and Mid() functions to retrieve the various elements shown.

strRecordType= Left([YourfieldNameGoesHere], 1)
lngSupplierID = Mid([YourfieldNameGoesHere], 2,5)

and so on.

Once you've parsed the string from each record in the input text, you can use the variables to insert the values into the appropriate table.

Thanks for your reply, the data doesn't have any field headers its just a string like shown above so all in one field table labelled Data

 

[theDBguy]

Hi. How many records per string of data are we talking about here?

 

[Number11]

Hi. How many records per string of data are we talking about here?

around 350k

 

[Gasman]

Are you sure you are not talking about 50k lines of text?

 

[ebs17]

Field contents are not separated by characters as delimiters, but rather as defined sections of the string => fixed lengths.

The division is quite easy if you use something like
DoCmd.TransferText acImportFixed, ...
(not acImportDelim)

You can assign fixed sections to the desired fields of the target table using the import specification to be created.

 

[theDBguy]

around 350k

If the data is not sensitive, can you post a sample file with your string, so we can see exactly what we're dealing with here? Thanks.

 

[Gasman]

Could you even use a specification?

 

[ebs17]

Could you even use a specification?

Yes, I have done that in the past.
In one case, even with a specification that had to be constantly adjusted using VBA because the creator of the text file tried to display the content as a readable table and therefore longer field contents resulted in wider columns (other fixed lengths).

The real work of a developer begins when creative suppliers don't stick to standards, but want to present their 25 years of school in individual representations.

 

[ebs17]

@Number11:
Are you getting along? Are there column headers in the text file?

Personally, I would not import such a text file, but rather link it and then transfer the contents to the table with an append query. Within the query, the text portions can then be appropriately formatted and converted so that a valid date actually appears in a date field.
You can regulate a lot of things within the query, e.g. that only new records are entered, that you give the import a timestamp and, if necessary, the source, and a few other things.

 

[Number11]

Thanks all so i have now imported the file and have set the specification imported a test file and looks good so thanks got it sorted now for the formatting