Importing 2007 into 2003

[chris benfield]

I am having trouble importing Access 2007 data into 2003, I am sent data from
a supplier who uses 2007 via a csv file. this creates too many fields for
access and Excel 2003 to cope with. Many of the fields are of no use to me,
but as the file is comma delited I cannot use fixed with to skip over the
unwanted fields when importing.

Is there any way for me to import this file and negate some of the unwanted
fields???

Please help!!!!

 

[gllincoln]

Hi Chris,

If he is a supplier, you are a customer; he should take better care of you.

It would be a matter of a few minutes effort for him to build a query to pull the columns you actually want and export that instead. Buy your products from me - I will send you the data in practically any format you care to see it. XML? Fixed? CSV? TSV? MDB? Only these 5 columns? No p-r-o-b-l-e-m, glad to be able to help you out with this.

Well - anybody who uses Access 2007 to create an export with so many fields that Access 2003 and Excel can't handle it - what can you expect? I wonder who he is trying to impress? Or mess with?

Okay - now that I've got that rant out of my system - are we really, really sure this is what you are dealing with? He is using a Wintel machine, using Access 2007, really does have that many columns of data in his export file?

Could this be one of those deals where the lines aren't breaking on the rows due to the differences between Linux to Windows to MAC in how they manage CrLf?

One way to find out (and sometimes fix the problem). Open up a DOS window/Terminal. Use the old DOS Edit to open the file, view it - count the header row - how many labels do you have? At the end of the row are you seeing data or does it break appropriately at the row? Just for giggles, save the file using Edit. This will *sometimes* fix the file, DOS Edit can read certain files and properly display them even when Notepad or an import wizard is choking on them. When you save with DOS Edit it will write the file using DOS style line breaks. I discovered this by accident a while back - working with PHP source code that seemingly had no line breaks.

If you have a genuine too many columns to fit 2003 file, then you need to pre-parse that file saving only the columns you want to keep. If you send me a sample of the data, the header and maybe ten rows or so, and the names of the columns you want to retain - I will play with it - build a function to strip away the extra stuff and make you a csv that you can import. Cutting and pasting data into this forum can cause issues due to the word wrapping etc. You can send a small attachment directly to gllincoln at live dot com.

Hope this helps,
Gordon

 

[chris benfield]

Afraid the data is far too sensitive to be sent out. its a national
collection point for data where I collect my info from, there's no asking
them to change how it comes.

 

[gllincoln]

Hi Chris,

In that case, the only reasonable way you are going to be able to fix the
column count issue is to write your own import utility, or hire the work
out.

Cordially,
Gordon