Importing a *.txt file to a Access database

[Phobbos]

I have attached a file which I exported from a access database. Now I need to find a way of importing that table again?.I am able to create a blank table with the right fields in place, for the *txt file I just need to beable to import the data. Anyone know how??

[BrandonSk]

Hi.

I had a problem about importing a txt into Access through code that I posted here about 10 days ago. Don't know what's the thread but here are some VERY useful links I got from R. Smith. Browse through it! It's worth it. If you're still stuck w/ the code then let me know and I'll write you the code for you.

Here are the links:
check some of the details in www.smithvoice.com/vb5expt.htm and
http://www.smithvoice.com/controll.htm and perhaps also you might like
http://www.smithvoice.com/rsconv.htm

[peet]

This sample shows u how u should open and read the file:

Option Explicit
 
Private Sub Command1_Click()
    Dim arrData() As String
    Dim s As String
    Dim i As Integer
    
    Open "c:\c1_1801t113036_testinga45minuterun.txt" For Input As #1
    Do While Not EOF(1)
        Line Input #1, s
        arrData = Split(s, ",")
        For i = 0 To UBound(arrData)
            Debug.Print arrData(i)
        Next i
    Loop
    Close #1
 
End Sub

Try the sample in a new project. step throug it and observe.

Now, in order to import the data u would have to put in some more code

eg:

Option Explicit
 
Private Sub Command1_Click()
    Dim arrData() As String
    Dim s As String
    Dim i As Integer
    
    Open "c:\c1_1801t113036_testinga45minuterun.txt" For Input As #1
    Do While Not EOF(1)
        Line Input #1, s
        arrData = Split(s, ",")
        'For each line u will add a new record to the table.
        'do this here.
        For i = 0 To UBound(arrData)
            'instead of debug.print, u should add the values to the
            'correct fields in the record. do that here.
            Debug.Print arrData(i)
        Next i
        'When u get here, a new record has been added. Update it 
        'so it is saved.
    Loop
    Close #1
 
End Sub

Hope this is of help to u.

[BrandonSk]

Peet's code is OK.
I still would use ISAM drivers though. If nothing else it is much faster (depends on your txt file size, my was 0.5GB!)

[peet]

Originally posted by BrandonSk
Peet's code is OK.
I still would use ISAM drivers though. If nothing else it is much faster (depends on your txt file size, my was 0.5GB!)

that is a big text file Brandon

I'v never used the ISAM way, thanks for the tip... do u know how much time savings are involved?

[BrandonSk]

Well, that is a big txt file. If you can imagine all calls in a small country like Czech Republic that are made in one moth /and I am not talking the # to # logs, just from a transmiter to transmiter (hour and amount of calls) / you will end up with such a text file

Anyway, to your question. I don't know the savings. Try on a few MB file and you'll see. I think if I did my txt your way, I'd be stuck for a long time. This way the import doesn't take that long (I don't know how much it takes even my way, I think I'll start measuring )

Post me a reply if you measure it, will ya? Thanx.

[Phobbos]

Originally posted by BrandonSk
Peet's code is OK.
I still would use ISAM drivers though. If nothing else it is much faster (depends on your txt file size, my was 0.5GB!)

This is what I have got so far, I will have a look at those websites you got, but have a look at this and see if you know how to complete it. Thanks

Private Sub cmdImport_Click()
Dim sFName As String
Dim sFmtsFName As String
'On Error GoTo ErrHandler
CommonDialog1.Filter = "Tempcontrol Files (*.*)|*.*"
CommonDialog1.FilterIndex = 2
CommonDialog1.ShowOpen
OpenFile = ("CommonDialog1.FileName")
sFName = CommonDialog1.FileTitle

With dePenlogs.cnlogs
If .State = 0 Then
.Open
End If
.BeginTrans
.Execute "CREATE TABLE " + sFName + "(" _
& "LogNo CHAR (20) NULL," _
& "Duration CHAR(20) NULL," _
& "Time_s CHAR(20) NULL," _
& "Value_s CHAR(20) NULL);"
.Execute "INSERT INTO " + sFName + "(LogNo, Duration, Time_s, Value_s)" _
& "VALUES()"
.CommitTrans
End With





Exit Sub

[BrandonSk]

I guess you're using ADO. Don't know much about it but I don't think that there'll be some major diferences (this uses SQL anyway).

So, if I got that right you first check if the file exists, if no, create a new table -> if that statement works, then it is ok.
Now with the export there's a little trick. I don't think your statement would work. You should create something like:
SELECT (fields) INTO [TypeOfDatabase; DATABASE=Path].[FlName] FROM Table;

So in your example something like this:
'st - string
'This just gets the Path. Didn't test it but should work
st=Left$(CommonDialog1.FileName, _
(Instr(CommonDialog1.FileName, CommonDialog1.FileTitle)-2)

Conn.Execute "SELECT TableName.LogNo, ...Duration, ...Time_s, ...Value_s INTO [Text; DATABASE=" & st & "].[" & sFName &"] FROM TableName;"

I don't know about INSERT INTO. Should work that way too.
Especialy pay attention to the Schema.ini file if you want specific output (e.g. delimiter etc.).

post back if not clear.