Importing More Than 255 Columns of Data

[CharlesWhiteman]

I have a project concerning lists of external files. The spread sheet holds the source data on a document per line basis. Each row contains vital data ClientID, ClientName, DocDate, DocDescription, MainPage, Page1, Page2, Page3.... up to Page 585

I have done previous work where importing up to 50 columns has never been an issue.

For some reason in this case I am unable to import more than up to 255 columns and also I lost all the reference data past column 30.

I have tried importing directly to a SQL Server Db - same issue
Access 2010 and this in 2013

Any advice on dealing with many (vital Columns) will be gratefully received

 

[CJ_London]

The maximum number of columns is 255 for a table or query. The only way round it would be to have multiple tables linked with a 1 to 1 relationship.

However you should not use Excel as a model for table design - in a database for what you describe you would typically have several tables along the following lines:

TblClient
ClientID autonumber PK
ClientName Text

TblDocuments
DocumentID autonumber PK
ClientID long FK
DocDate Date
DocDescription text

TblPages
PageID autonumber PK
DocumentID long FK
PageDescription text e.g.mainpage, page1, page2 etc
PageContents Memo

Unfortunately this does mean it is not a straightforward import - can explain further if you decide to go this route

 

[Pat Hartman]

To expand on what CJ said, you are going to have to save the spreadsheet as .csv. Then open the .csv using fso (File System Objects) to read it. Your code will need to parse the input file and put the various columns into the correct table (using DAO and the .addNew method).

I don't have any code samples handy but hopefully, I've given you enough key words so your search will find some for you.

 

[CharlesWhiteman]

Thanks CJ,

It's not a massive issue, in this case, to alter the spread sheet and import multiple sheets ImportID plus the columns and I can work with that in access