[RESOLVED] from text file to access table

[lall]

Hello there,

Can anyone help me to write a module, that takes a text file and splits the text row by row into access table.

This is a sample row from the text file:
F2009021908362600111154050110230000203024121274000000000001000

and all the rows are alike, and i need it to split:

1, 2-5, 6-7, 8-9, 10-11, 12-13, 14-19, 20-25, 26-28, 29-31, 32-34 ,35-47, 48-54, 55-61 and 62 characters into access table in separate columns as i described.


And IO is my weakest link..

Thank you,
Martin

[Ecniv]

Using VBA or API?

VBA - functions to read / search:
open
line input
close
eof
left
right
mid <<<< you will probably use this


If your text is always the same (as in fixed length) then you should be able to loop thorugh the file, importing a line, separating it and placing into the fields before closing the file and recordset.


Have a go and post up some code if you can't get it working.

[lall]

Using VBA or API?

VBA - functions to read / search:
open
line input
close
eof
left
right
mid <<<< you will probably use this


If your text is always the same (as in fixed length) then you should be able to loop thorugh the file, importing a line, separating it and placing into the fields before closing the file and recordset.


Have a go and post up some code if you can't get it working.

Hi!

Thank you for your reply.
I'm using Visual Basic 6.

Yes, the text is always the same.

I know what should i do, but i just don't have no knowledge about IO commands.

I thought there is a sample to show by someone.

[si_the_geek]

In terms of getting the data from the file, see the "Files" section of our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)

You can then use Left/Right/Mid to get the parts of the data you are interested in.


Once you have that all working properly, you can start on the database part.

[lall]

In terms of getting the data from the file, see the "Files" section of our Classic VB FAQs (in the FAQ forum, which is shown near the top of our home page)

You can then use Left/Right/Mid to get the parts of the data you are interested in.


Once you have that all working properly, you can start on the database part.

Managing DB is not problem for me, only the reading from file part.
Thanks for the link, i'll look into it.

Martin

[lall]

And here it is, made by myself

Public Sub test()

Dim NewTable As TableDef
Dim strCode As String
Dim iFileNo As Integer

'load database
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\dbase.mdb", _
False, False)

'create new table
Set NewTable = db.CreateTableDef("pdt_data")
On Error Resume Next
'delete the table if it already exists
db.TableDefs.Delete NewTable.Name

'add the fields in the table
With NewTable
  .Fields.Append .CreateField("F", dbMemo, 5)
  .Fields.Append .CreateField("YEAR", dbText, 30)
  .Fields.Append .CreateField("MONTH", dbText, 30)
  .Fields.Append .CreateField("DATE", dbText, 30)
  .Fields.Append .CreateField("HR", dbText, 30)
  .Fields.Append .CreateField("MIN", dbText, 30)
  .Fields.Append .CreateField("KPY", dbText, 30)
End With
db.TableDefs.Append NewTable
'set table
Set rs = db.OpenRecordset("pdt_data")

iFileNo = FreeFile

'open the file for reading
Open App.Path & "\PDT\pdt.dat" For Input As #iFileNo

'read the file until we reach the end
Do While Not EOF(iFileNo)
Input #iFileNo, strCode

'write into database
rs.FindLast
rs.MoveNext
rs.AddNew
rs!F = Mid(strCode, 1, 1)
rs!Year = Mid(strCode, 2, 4)
rs!Month = Mid(strCode, 6, 2)
rs!Date = Mid(strCode, 8, 2)
rs!hr = Mid(strCode, 10, 2)
rs!Min = Mid(strCode, 12, 2)
rs!kpy = Mid(strCode, 14, 5)
rs.Update

Loop

'close the file (if you dont do this, you wont be able to open it again!)
Close #iFileNo

'close database
db.Close

End Sub

So it's not 100% ready, but it's not hard to finish it. Just i don't remember what's all those pieces are meaning in my code :P
So final piece is the "26001" in the code.

Hope that helps anybody,
Good luck and thank you!

Martin

[lall]

Anyone knows how can i make vb to read by 2 lines:

So that first 2 lines are handeled as 1 line and second 2 lines as one line and so on...or something like that.

This is a row from the text file:

1000999999027LDS GLOVE NAPPA WITH SYNT SILK322150MCREME 6.5 N0902190000230000000111720000000+0000000+ 0000000000000000+0203018136437
200099999902702030181364370000000000000

It's fixed length, always.
And i need it to be read into access onto one row.
And then loop through the text file.

All the rows are starting with 1000 and 2000.

Longer sample:
1000999999027LDS GLOVE NAPPA WITH SYNT SILK322150MCREME 6.5 N0902190000230000000111720000000+0000000+ 0000000000000000+0203018136437
200099999902702030181364370000000000000
1000999999027LDS GLOVE NAPPA WITH SYNT SILK322150MCREME 7.0 N0902190000230000000111720000000+0000000+ 0000000000000000+0203018136444
200099999902702030181364440000000000000
1000999999027LDS GLOVE NAPPA WITH SYNT SILK322150MCREME 7.5 N0902190000230000000111720000000+0000000+ 0000000000000000+0203018136451
200099999902702030181364510000000000000
1000999999027LDS GLOVE NAPPA WITH SYNT SILK322150MCREME 8.0 N0902190000230000000111720000000+0000000+ 0000000000000000+0203018136468
200099999902702030181364680000000000000
1000999999027LDS BAG TOTE SUEDE WITH LINING322161MBEIGE *** N0902190000200000000206350000000+0000000+ 0000000000002500+0203018193096
200099999902702030181930960000000000000
1000999999027LDS BAG TOTE SUEDE WITH LINING322161MAQUA *** N0902190000100000000206350000000+0000000+ 0000000000000000+0203018195519
200099999902702030181955190000000000000
1000999999027LDS BAG TOTE SUEDE WITH LINING322161MWHITE *** N0902190000595000000206350000000+0000000+ 0000000000000000+0203018195526
200099999902702030181955260000000000000
1000999999027LDS GLOVES LAMM SKIN WITH SILK322150MBLACK 6.5 N0902190000525000000202620000000+0000000+ 0000000000001000+0203018268817
200099999902702030182688170000000000000
1000999999027LDS GLOVES LAMMNAPPA WITH SILK322150MBLACK 6.5 N0902190000525000000195580000000+0000000+ 0000000000001000+0203018268824
200099999902702030182688240000000000000

[si_the_geek]

To read an entire line, change Input to Line Input:

Line Input #iFileNo, strCode

To read two lines, you need to use a second variable:

Line Input #iFileNo, strCode
Line Input #iFileNo, strCode2
strCode = strCode & vbNewLine & strCode2

[lall]

To read an entire line, change Input to Line Input:

Line Input #iFileNo, strCode

To read two lines, you need to use a second variable:

Line Input #iFileNo, strCode
Line Input #iFileNo, strCode2
strCode = strCode & vbNewLine & strCode2

Hey, thanks again.
It seems to be working.
I'll post my code if it's ready, maybe it'll help someone.

Martin

[lall]

Public Sub importmove()

Dim NewTable As TableDef
Dim strCode As String
Dim iFileNo As Integer

'load database
Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\dbase.mdb", _
False, False)

'create new table
Set NewTable = db.CreateTableDef("move_data")
On Error Resume Next
'delete the table if it already exists
db.TableDefs.Delete NewTable.Name

'add the fields in the table
With NewTable
  .Fields.Append .CreateField("A", dbText, 4)
  .Fields.Append .CreateField("B", dbText, 6)
  .Fields.Append .CreateField("C", dbText, 3)
  .Fields.Append .CreateField("INFO", dbText, 30)
  .Fields.Append .CreateField("MG", dbText, 3)
  .Fields.Append .CreateField("PG", dbText, 3)
  .Fields.Append .CreateField("M", dbText, 1)
  .Fields.Append .CreateField("COLOUR", dbText, 6)
  .Fields.Append .CreateField("EMPTY_20", dbText, 20)
  .Fields.Append .CreateField("SIZE", dbText, 3)
  .Fields.Append .CreateField("EMPTY_6", dbText, 6)
  .Fields.Append .CreateField("N", dbText, 1)
  .Fields.Append .CreateField("YY", dbText, 2)
  .Fields.Append .CreateField("MM", dbText, 2)
  .Fields.Append .CreateField("DD", dbText, 2)
  
  .Fields.Append .CreateField("MH", dbText, 9)
  .Fields.Append .CreateField("DDP", dbText, 9)
  .Fields.Append .CreateField("MOVE_QTY", dbText, 7)
  .Fields.Append .CreateField("MOVE_QTY_SIGN", dbText, 1)
  .Fields.Append .CreateField("ZERO_7", dbText, 7)
  .Fields.Append .CreateField("ZERO_7_SIGN", dbText, 1)
  .Fields.Append .CreateField("EMPTY_1", dbText, 1)
  .Fields.Append .CreateField("ZERO_16", dbText, 16)
  .Fields.Append .CreateField("ZERO_16_SIGN", dbText, 1)
  .Fields.Append .CreateField("MOVECODE", dbText, 13)
  .Fields.Append .CreateField("L2", dbText, 10)
  .Fields.Append .CreateField("L2_DDP", dbText, 3)
  
  .Fields.Append .CreateField("MOVECODE_COPY", dbText, 13)
  .Fields.Append .CreateField("ALTCODE1", dbText, 13)
  .Fields.Append .CreateField("ALTCODE2", dbText, 13)
  .Fields.Append .CreateField("ALTCODE3", dbText, 13)
  .Fields.Append .CreateField("ALTCODE4", dbText, 13)
  .Fields.Append .CreateField("ALTCODE5", dbText, 13)
  .Fields.Append .CreateField("ALTCODE6", dbText, 13)

    
End With
'save table headers
db.TableDefs.Append NewTable

'set table
Set rs = db.OpenRecordset("move_data")

iFileNo = FreeFile

'open the file for reading
Open App.Path & "\MOVE\move.27" For Input As #iFileNo

'read the file until we reach the end
Do While Not EOF(iFileNo)
Line Input #iFileNo, strCode
Line Input #iFileNo, strCode2
strCode = strCode & vbNewLine & strCode2

'write into database
'and sort the code into pieces
rs.FindLast
rs.MoveNext
rs.AddNew

rs!A = Mid(strCode, 1, 4)
rs!B = Mid(strCode, 5, 6)
rs!C = Mid(strCode, 11, 3)
rs!INFO = Mid(strCode, 14, 30)
rs!MG = Mid(strCode, 44, 3)
rs!PG = Mid(strCode, 47, 3)
rs!M = Mid(strCode, 50, 1)
rs!COLOUR = Mid(strCode, 51, 6)
rs!EMPTY_20 = Mid(strCode, 56, 20)
rs!Size = Mid(strCode, 76, 3)
rs!EMPTY_6 = Mid(strCode, 79, 6)
rs!N = Mid(strCode, 85, 1)
rs!YY = Mid(strCode, 86, 2)
rs!MM = Mid(strCode, 88, 2)
rs!DD = Mid(strCode, 90, 2)
rs!MH = Mid(strCode, 92, 9)
rs!DDP = Mid(strCode, 101, 9)
rs!MOVE_QTY = Mid(strCode, 110, 7)
rs!MOVE_QTY_SIGN = Mid(strCode, 117, 1)
rs!ZERO_7 = Mid(strCode, 118, 7)
rs!ZERO_7_SIGN = Mid(strCode, 125, 1)
rs!EMPTY_1 = Mid(strCode, 126, 1)
rs!ZERO_16 = Mid(strCode, 127, 16)
rs!ZERO_16_SIGN = Mid(strCode, 143, 1)
rs!MOVECODE = Mid(strCode, 144, 13)

'it seems that line switching takes 2 char. spaces!
rs!L2 = Mid(strCode, 159, 10)
rs!L2_DDP = Mid(strCode, 169, 3)

rs!MOVECODE_COPY = Mid(strCode, 172, 13)
rs!ALTCODE1 = Mid(strCode, 185, 13)
rs!ALTCODE2 = Mid(strCode, 198, 13)
If Mid(strCode, 198, 13) = "" Then
rs!ALTCODE2 = "0000000000000"

rs!ALTCODE3 = Mid(strCode, 211, 13)
If Mid(strCode, 211, 13) = "" Then
rs!ALTCODE3 = "0000000000000"

rs!ALTCODE4 = Mid(strCode, 224, 13)
If Mid(strCode, 224, 13) = "" Then
rs!ALTCODE4 = "0000000000000"

rs!ALTCODE5 = Mid(strCode, 237, 13)
If Mid(strCode, 237, 13) = "" Then
rs!ALTCODE5 = "0000000000000"

rs!ALTCODE6 = Mid(strCode, 250, 13)
If Mid(strCode, 250, 13) = "" Then
rs!ALTCODE6 = "0000000000000"

End If
End If
End If
End If
End If



rs.Update

Loop

'close the file (if you dont do this, you wont be able to open it again!)
Close #iFileNo

'close database
db.Close
frmmain.Label1.Caption = "Ready!"
End Sub

Note that there is a comment about changing a line. It seems that the process takes 2 char. spaces

That's it. Thank you again si_the_geek

[si_the_geek]

A new line indicator in Windows is two characters - Carriage Return (ascii code 13) followed by Line Feed (ascii code 10).

Other operating systems use different variations of CR and/or LF, but you don't need to worry about that.