[AccessD] URGENT HELP importing a very large CSV file (many Fields)

On 19 Feb 2004 at 17:14, Tom Keatley wrote:

> Hi all....
> 
> I have a CSV file that I need to get information from and build a table in
> Access  ....it contains 600 fields and of course I cant simply import it as
> it hits the access limit of 255 fields and will not import the whole file
> 
> It is simply an Order for goods with 150 or so records but from 500
> different locations.
> 
> so ...
> 
> Fields in the CSV file
> [Item ] [itemcode][storeid1][storeid2][storeid3][storeid4][storeid5]etc to
> 600 fields
> 
> I need to bring those into access and populate a table so....
> [Item ] [itemcode][storeid1]
> [Item ] [itemcode][storeid2]
> [Item ] [itemcode][storeid3]
> [Item ] [itemcode][storeid4]
> [Item ] [itemcode][storeid5]
> [Item ] [itemcode][storeid6]
> etc
> using the data contained in [Item ] [itemcode] fields and using the
> fieldname from the 500 odd [storeid] fields as my store id.
> 
> I have been playing with this for some hours so far and dont seem to be able
> to acheive what I need
> 
You need something like the following  (air code, so will proably 
need some tweeking). Note this use DAO, ADO would be similar

Dim strTemp as string
Dim rs as DAO.Recordset
Dim myArray() as String
Dim Loopcount as long


Set rs = Currentdb.OpenRecordset("tblMyTable")
Open "MyFile.CSV" for input as #1

While Not EOF(1)

'Read the next line in
Line Input #1, strTemp

'Split the fields into an Array
myArray = Split(strTemp,",")

'Loop through the elements of the Array and create then the records
For Loopcount = 2 to Ubound(myArray)
    rs.AddNew
    rs(0) = myArray(0) 'Item
    rs(1) = myArray(1) 'ItemCode
   rs(2) = Loopcount - 1  'StoreID
   rs(3) = myArray(Loopcount) 'Qty
   rs.Update
Next
Wend


 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.