850 field import
- Thread starter gsbatch1
- Start date
- Local time
- Today, 05:15
- Joined
- Feb 19, 2013
- Messages
- 17,566
the 250 maximum is per table so you need to split your import data.
Ideally create normalised tables and do an import for each table using SELECT DISTINCT or group by to eliminate duplicates.
If this can't be done, create a unique ID for each row in your source if one does not exist at the moment, then create say 5 tables ensuring each table contains the unique id and do 4 imports - 1st for the first 200 columns + ID field, 2nd for the next 200 columns + ID field, etc
Ideally create normalised tables and do an import for each table using SELECT DISTINCT or group by to eliminate duplicates.
If this can't be done, create a unique ID for each row in your source if one does not exist at the moment, then create say 5 tables ensuring each table contains the unique id and do 4 imports - 1st for the first 200 columns + ID field, 2nd for the next 200 columns + ID field, etc
Yes I have already dumped it into excel and created a tab for each (Table) I would want. The issue is its a daily process, and manually manipulating the data into excel and slicing it up to different tabs will take too long each day. I would like to just be abole to click a macro, and it imports the right fields into the right tables.
- Local time
- Today, 00:15
- Joined
- Feb 19, 2002
- Messages
- 47,594
You can resort to the old text file imports and import the file manually as long as it is a plain text file format. Look up Open, Close, input for code samples. You can then take each column and append it to whatever table is appropriate.
Keep in mind that if you don't normalize this data, you won't be able to work with it. Tables are limited to 255 columns but so are queries and so you won't be able to join multiple tables and exceed that column limitation. There is also a limit to the number of controls on a form/report. I don't know offhand what it is since I have never come even close to it but that could bite you also.
I'm guessing that if you normalize the schema, you will not have any problems. If you don't know how to normalize it, you could try posting back for ideas.
Keep in mind that if you don't normalize this data, you won't be able to work with it. Tables are limited to 255 columns but so are queries and so you won't be able to join multiple tables and exceed that column limitation. There is also a limit to the number of controls on a form/report. I don't know offhand what it is since I have never come even close to it but that could bite you also.
I'm guessing that if you normalize the schema, you will not have any problems. If you don't know how to normalize it, you could try posting back for ideas.
RainLover
VIP From a land downunder
- Local time
- Today, 14:15
- Joined
- Jan 5, 2009
- Messages
- 5,041
Why 850 fields.
Someone wrote the code to extract the Data from a different database so they should adjust that code to suit your needs by creating a series of downloads that match your tables.
The other possibility is for you to get Read Only access to that database and import the data directly into access.
If you can't get support to do either of the above you will have to write your own code in VBA. This would be a task that I would not like to take on as it would be quite large and require a lot of testing. If you go this way then break it down into a series of routines and call each one in turn. This should make debugging easier.
Someone wrote the code to extract the Data from a different database so they should adjust that code to suit your needs by creating a series of downloads that match your tables.
The other possibility is for you to get Read Only access to that database and import the data directly into access.
If you can't get support to do either of the above you will have to write your own code in VBA. This would be a task that I would not like to take on as it would be quite large and require a lot of testing. If you go this way then break it down into a series of routines and call each one in turn. This should make debugging easier.
You wouldn't use 850 fields anywhere. Normalize the data into related tables. Avoid Excel althogether as it isn't going to help at all. I wouldn't bother with the Access import either as this would be very clumsy and repetitive.
Assuming the original data is a text file, read and parse line by line, writing the values directly to the relational table structure.
Can you provide us with a small sample of the original records and explain their meaning if it isn't obvious?
Assuming the original data is a text file, read and parse line by line, writing the values directly to the relational table structure.
Can you provide us with a small sample of the original records and explain their meaning if it isn't obvious?
