Import text file.

[Steve_in_Kent]

I have started to write a database, that imports a text file, into a table. (the text file comes from another application)

the text file has strange formatting, so the code, sorts everything into a logical format for the table.

currently, the data all comes in as a string. (and mostly, left justified, bar one field).

in this data, i have a date field, a time field, and a number field. that i would prefer be in the database, as that data type in the table so i can use it properly in queries etc.

my question is, whats the simplest way of making this happen. ? - this is a sample of a the data...

Date field = "03/10/10" (but left justified) i think its always 8 chars.

Time field = "1400", again left justified, and always 4 chars

numberfield1 ="9960864301" - i believe its always the same number of chars, but not certain, and its left justified.

numberfield2 = "59233", - can be any number, text field, but its kind of left justified.
Steve

[HansV]

The main problem appears to be the time field - if you want to be able to perform calculations on it, you'd have to convert it to a real date/time field that contains 14:00 instead of 1400. What is the purpose of the table?

[Steve_in_Kent]

Purpose is to provide quantity totals, based on the quantity field, per stock item.

likely to involve the time and date as well in the queries.

the last field, can be a negative number as well, and i currently have it importing, as " 45029 ", " -332 " left justified. so there are additional spaces here and there.

[HansV]

Does "numberfield1" contain leading zeros? And if so, are these relevant (for example because it identifies a stock item)? In thas case it would be best to make it a text field even if it looks like a number.
Since "numberfield2" can be negative, I assume it is a quantity, so that should be converted into a number.

[Steve_in_Kent]

No. as far as im aware.. the part number, has no leading zeros.

the numberfield 2 string, is a bit weird.,, in that the way i grab it from the text file, dicates it has leading and trailing spaces. (i think! ).. heh

[HansV]

Numberfield1 is too long for a Numeric (Long) field anyway, so I'd keep it as text.
I would do the following:

Create a new table with three fields:
- A date/time field to hold both the date and the time from the text file.
- A text field to hold numberfield1.
- A number (long integer) field to hold numberfield2.

Import or link the text file to a separate table, then create an append query that transforms the data from the text file and appends them in the correct format to the Access table.

[Steve_in_Kent]

im bringing the text into a string, in VBA.

can i turn it into the format i want in VBA?

im then creating an sql string and adding a row of data into the table.

strSql = "INSERT INTO mytable ( field1, field2, etc etc.

[HansV]

That should be possible but I need more detailed information. You get one long string? Could you post or attach a sample?

[Steve_in_Kent]

The data is a string yes.. and i split it out.

i'm current'y using this:-

Code: Select all

CincDateString = Mid(linetxt, 9, 8)

CincomTimeString = Mid(linetxt, 21, 4)
CincomTime = Left(CincomTimeString, 2) + ":" + Right(CincomTimeString, 2)

Code: Select all

CincDate = DateValue(CincDateString)
CincomQty = CLng(Nz(CincomQtyString, 0))

So im changing the text string, for date, using Datevalue, and Clng, for the number.

for data and time, would i be better merging it into just 1 field so, that in can be better used in queries?

[HansV]

Yes, I'd use a single date/time field

CincDate = DateValue(CincDateString) + TimeValue(CincomTime)

[Steve_in_Kent]

Yep.. have done exactly that Hans.. thanks.