Importing non access text files 3

[harmmeijer]

I am using an application which exports its data as a text file, as separators a ; is used and a , for decimal separator.
The file is of variable length depending on how many records it contains. When I manually import this file access recognises the ; as separators and the , for decimal separator but when I use a vba code it does not work because the format is wrong.
Does anybody know a code to import this file?

I filled in that I am a programmer but that is whish full thinking.

 

[jimmythegeek]

Import it manually once, but while you are doing it, save the import specifications.

Then use the TransferText method to import the file, with the specs that you saved, as one of the arguments. Jim Lunde
compugeeks@hotmail.com
Custom Application Development

 

[Guest_imported]

This has done the trick, perfict and so simple once you know about it.

 

[Quehay]

I've never done it as J the G describes (although based on your response I should). Another way of importing text files is to drop them into Excel from the clipboard, and then import the XL data. The advantage of going into Excel is being able to visually scan large sections of data and check for inconsistencies, e.g., "N/A" in a date field, "None" in a number field, etc. I haven't yet figured out a good trick to get rid of zero length strings, which seem to propagate when untutored users enter data into sheets & docs.

 

[harmmeijer]

Ok Quehay I can see the problem, if you're not sure the text file contains correct info like none in a numeric field or n/a in a date you should import the text in a table with all text coulombs which can be empty.
Then put this table into a record set, put the destination table in a record set en update the destination table checking the source record set (which was the text file) with some if statements.

Function test()
' specify some variables as date or integer so we can do a check later
Dim datevar As Date
Dim integervar As Integer

' you should have a table called temp or the next line will get an error.
‘ in this table we will import the text file
DoCmd.DeleteObject acTable, "temp"

' you should have a saved specs j the g is talking about (in this case its called imp) and put the text file
' in a table called temp
' this table will be made when it is not there
DoCmd.TransferText acImportDelim, "imp", "temp", "c:\backup\test.txt"

' now we are going to open the temp table and put it in a recordset called rstcource
Set rstcource = CurrentDb.OpenRecordset("temp&quot

' now we are going to open a table called ebanking in a recordset called rstdest this is the table where
‘ the data from the text file should go
Set rstdest = CurrentDb.OpenRecordset("ebanking&quot

' now we are going to open a table called errors where we put the record with the wrong format
‘ this table should contain 2 text fields which can be zero length and are called numberfield and
‘ datefield
Set rsterr = CurrentDb.OpenRecordset("errors&quot

' now lets put the source in the destination table
Do Until rstcource.EOF

' the on error statement below jumps to wrong_format if something goes wrong so the record in the table wil not be filled
' allthough if you use a long integer (between -32768 till 32767) you get a script error so an if statement is needed
' this is also the case when the text record was empty
On Error GoTo wrong_format
If rstcource.numberfield > 32767 Or rstcource.numberfield < -32768 Then
GoTo wrong_format
Else
If Not IsNull(rstcource.datefield) Then
datevar = rstcource.datefield
Else
GoTo wrong_format
End If
If Not IsNull(rstcource.numberfield) Then
integervar = rstcource.numberfield
Else
GoTo wrong_format
End If

‘ here the destination table is filled if nothing is wrong, otherwise the error table is filled
rstdest.AddNew
rstdest.numberfield = integervar
rstdest.datefield = datevar
rstdest.checkfield = check
rstdest.Update

‘ here we need to jump to the end otherwise the error record will be filled with all records both good
‘ and bad
GoTo endloop
End If
wrong_format:

‘ here the error table is filled
rsterr.AddNew
rsterr.datefield = rstcource.datefield
rsterr.numberfield = rstcource.numberfield
rsterr.Update

‘ endloop is just a tag to jump to this does not mean the loop end here, the loop ends when there are no more
‘ records in the recordset rstcource
endloop:

‘ goto the next record in rstcource this is verry important because if this does not happen the loop will
‘ never stop
rstcource.MoveNext
Loop
End Function

 

[Quehay]

harmmeijer,

Thanks for your reply!! Access actually does a &quot;import errors&quot; table that will show record#/field import problems in some situations. However in other instances it will just tell you that a table couldn't be imported in x number of records because of validation errors without specifying which fields/records. Making this overt data checking routine is probably a much better way to go!