0

I am trying to load a text file into an Access 2007 table. I know you can read the file line by line and then create a record out of each line. i was trying to see if this could be done with an INSERT INTO rather than cyclying through all lines of text. My text file is not character delimited but rather by fixed column width. For example:

Date  Speed    Weight  CarID    Fuel
1120  200      10000   T230     200
1112  215      11000   F3AE     160

The data in the example has spaces for readability but in reality the data are clumped together like so 

112020010000T230200
111221511000F3AE160

Anyway i was attempting 

    Dim sImportFolder As String = "C:\MyData"
    Dim sSource As String = "C:\data.accdb"
    Dim sImportFile As String = "week.txt"

    Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & sSource & ";Persist Security Info=True;Jet OLEDB:Database Password=blah")
    AccessConn.Open() 'open the connection to the database

    Dim AccessCommand As New System.Data.OleDb.OleDbCommand("INSERT INTO [tblData] ([PtDate], [PtSpeed], [PtWt], [PtCar], [PtFuel]) SELECT F1, F2, F3, F4, F5 FROM [Text;DATABASE=" & sImportFolder & ";].[" & sImportFile & "]")
    AccessCommand.Connection = AccessConn
    AccessCommand.ExecuteNonQuery()

    AccessConn.Close()

I cant figure out how to tell the command how the data is structured. I know you can use a schema file but there's got to be a way to do this all through code.

AGP

sinDizzy
  • 1,300
  • 7
  • 28
  • 60

1 Answers1

0

There is a similar question on SO here:

Read fixed width record from text file

Basically, the answer is that there isn't something simple you can do in the code to specify the schema and have it broke up for you. What you would need to do is either loop through each row, pulling out the data using SubString and then doing one insert into Access per row (not terribly efficient) or you could build a DataTable in the loop and then do an insert into the Access database using the DataTable. To build the DataTable, you will still need to parse your data (either using SubString or a RegEx).

Community
  • 1
  • 1
IAmTimCorey
  • 16,412
  • 5
  • 39
  • 75
  • I'm confused by all of this. Access/Jet/ACE has no trouble reading fixed-width files, as long as you define the column widths. This can be done with the import wizard from within Access and then the import spec (and/or the schema.ini file) can then be used in code from outside Access. – David-W-Fenton May 07 '11 at 20:58
  • ok i gotcha. but I really wanted to approach it without a dependence on an internal Access spec or a schema.ini file. Maybe that cant be done and in that case i may have no choice. – sinDizzy May 08 '11 at 01:10
  • I can't think of any way to do it except to open the file directly and walk through. But you'd still have to know where the column boundaries are to make anything out of it, so I can't see how that approach would be superior to the alternatives outlined. – David-W-Fenton May 12 '11 at 00:52