Limits on Saved Import specifications

[Alc]

I have a daily report I will need to import into Access from a csv file. The report has around 650 columns, of which I will (initially) require around 60.

My first though was to create a an import spec. and call it as needed. I was under the impression that I could just specify which columns to import and, as I need way under the 255 limit, that would work.

What I've found, however, is that when I try to set up the import specification it looks like I'm only able to choose from the first 255 columns, not from the whole sheet.

1. If I want, let's say, columns 1 - 10, 75, 150 and 400 is this possible?

2. If I can't do this using the above method, my next idea was to
a) Import the first batch of 100 columns into a temporary table.
b) Copy columns I need into a 'final' table
c) Delete the temp table
d) Import the next batch of 100 into a temp table.
e) Update the records I copied across in step b
f) repeat steps c to e for the whole csv file
I'm a little concerned about the update sections. What would be the safest way to ensure that I'm updating the correct records?

 

[Alc]

Additional, related question:

If two tables are created by importing data from a csv file using TransferText, then Alter Table is used to assign an AutoIncrement value to both tables, will record 1 from the first table always correspond to record 1 from the second, record 20 to record 20, and so on down to the final record in each table? If so, this solution will work for me.

 

[static]

#1 You would be better off doing some pre processing to create a file in the format you require. e.g.

Read the file contents to string, use the split function to create an array. Remove data that you don't need.
Use the join function to merge everything back together and save to a new file. Import the new file.

#2 Yes if there is no other order/index applied.

 

[static]

edit++

Actually if you are creating the auto increment from scratch every time, order probably wouldn't make any difference since it's really just a display thing.

You would need to recreate the field though, since indexes aren't reset except during compact/repair.

 

[Alc]

edit++

Actually if you are creating the auto increment from scratch every time, order probably wouldn't make any difference since it's really just a display thing.

You would need to recreate the field though, since indexes aren't reset except during compact/repair.

Thanks for the response.

"order probably wouldn't make any difference since it's really just a display thing."
Does this mean that if I have two tables, created from columns 1 - 100 and 101 - 200 of the same spreadsheet, I might end up with an entirely different auto increment value in each table for what was row 10 on the original spreadsheet?

 

[sneuberg]

Does this mean that if I have two tables, created from columns 1 - 100 and 101 - 200 of the same spreadsheet, I might end up with an entirely different auto increment value in each table for what was row 10 on the original spreadsheet?

Unless rows are somehow skipped I don't see how that could happen. You could run an unmatched query just to make sure rows aren't missing.

 

[static]

The first set of records you put into the tables will be fine.
If you deleted the records and did another import the increment would start from 11 not 1. Again the records would match up ok. But you would have to be absolutely sure that the same number of records are added and deleted every time and no other process modified the tables in any way otherwise you couldn't be sure that increments would always match.

 

[sneuberg]

Or you could delete and recreate the tables each time. In any case this is probably going to cause some bloat. I suggest doing a compact and repair somewhere in this process. I believe if you do a compact and repair on an empty table that the autonumbers are reset.

 

[Alc]

The first set of records you put into the tables will be fine.
If you deleted the records and did another import the increment would start from 11 not 1. Again the records would match up ok. But you would have to be absolutely sure that the same number of records are added and deleted every time and no other process modified the tables in any way otherwise you couldn't be sure that increments would always match.

I'm planning on getting around that increment part by dropping and recreating the tables each time, ensuring they start from 1. As long as I can make sure the data for a given row number in all tables matches up correctly, I think I'll be good.

Going back to your query idea, I was already using it to check on the number of records imported, but I think I'll do a final check by exporting the end result to Excel and using a formula to compare the results, cell by cell.

 

[static]

Well speaking of Excel, you could try converting the csv into an Excel file and use the range parameter of transferspreadsheet to get the fields into one table.

 

[Alc]

Well speaking of Excel, you could try converting the csv into an Excel file and use the range parameter of transferspreadsheet to get the fields into one table.

That was the first thing I tried, back when I started on this problem. I still couldn't work out a way to bring in the whole table? The 255 limit seems to apply here, too.