Need to map and import into relational access database from a flat file having the many relationships as columns

Question

0.00/5 (No votes)

See more:

SQL-Server-2014

We have been running for years a custom Access database populated from tables (weekly mdb file) downloaded from a national portal for college applicants. This national portal has eliminated the .mdb file data and replaced it with flat/horizontal csv file exports. Instead of tables supporting one to many records, the csv has one record and the"many" fields are now just new columns on the one csv record line. Now, a full export will have some 1300 columns for any one applicant (we have thousands of applicants).

         For example:

old .mdb files:

Applicant   School
556321     Dartmouth
556321     Duke
556321     Clemson
556321     Auburn
556321     Vanderbilt

new csv files:

Applicant     School_0     School_1     School_2     School_3     School_4
556321        Dartmouth      Duke           Clemson      Auburn       Vanderbilt

I would like to map and import this new horizontal csv file (with each applicant on one row and it's many attributes as new columns) into our Access Database. Mapping the many columns into different tables and establishing the relational and vertical nature as in the "old" example above. We need to import new data weekly, so this is an ongoing procedure.

Thanks for any help offered.
-tony

[EDIT] additional information after solution 1 copied from non-solution below[/Edit]
Jörgen,

Thank you... I do have SQL-Server 2014 & 2012, so I tagged my question to open up solution scope to include SQL Server. Although my Access 2010 database front and back ends are built out and work fine prior to this draconian data delivery change which I cannot control. So I did not want to re-invent the wheel on what works. Although, I agree with your comment "use a real database".

Your answer is excellent. SQL Unpivot is better way to go (Unpivot is new to me... need to brush up on my SQL). In the long run I should move everything to SQL, but time and other projects might necessitate me using Unpivot in SQL and export tables to Access in short run. My sample data worked great with Unpivot in SQL.

Quick followup question...

I get errors when importing full CSV file into SQL Server on too many columns.

VB

TITLE: SQL Server Import and Export Wizard
    ------------------------------
        Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE [dbo].[@Wrkpc_Export_csv] (
        [cas_id]..." failed with the following error: "CREATE TABLE failed because column 'patient_care_experience_employer_name_16' in table '@Wrkpc_Export_csv' exceeds the maximum of 1024 columns.".

-- Do I need to break my exports into smaller CVS files?
-- Or can I download one very big csv file with 2833 columns and Unpivot with SQL in some other manner?
I prefer one big CSV file if feasible.
(the data actually is more than colleges attended as given in example, it also has courses, grades, addresses, ect... all on one line for each applicant.)

Thank you for the great answer and help!
-tony

Posted 12-May-14 6:14am

Member 10812903

Updated 13-May-14 12:01pm

Nelek

v2

Add a Solution

2 solutions

Solution 2

Jörgen,

Thank you... I do have SQL-Server 2014 & 2012, so I tagged my question to open up solution scope to include SQL Server. Although my Access 2010 database front and back ends are built out and work fine prior to this draconian data delivery change which I cannot control. So I did not want to re-invent the wheel on what works. Although, I agree with your comment "use a real database".

Your answer is excellent. SQL Unpivot is better way to go (Unpivot is new to me... need to brush up on my SQL). In the long run I should move everything to SQL, but time and other projects might necessitate me using Unpivot in SQL and export tables to Access in short run. My sample data worked great with Unpivot in SQL.

Quick followup question...

I get errors when importing full CSV file into SQL Server on too many columns.

VB

TITLE: SQL Server Import and Export Wizard
    ------------------------------
        Error 0xc002f210: Preparation SQL Task 1: Executing the query "CREATE TABLE [dbo].[@Wrkpc_Export_csv] (
        [cas_id]..." failed with the following error: "CREATE TABLE failed because column 'patient_care_experience_employer_name_16' in table '@Wrkpc_Export_csv' exceeds the maximum of 1024 columns.".

-- Do I need to break my exports into smaller CVS files?
-- Or can I download one very big csv file with 2833 columns and Unpivot with SQL in some other manner?
I prefer one big CSV file if feasible.
(the data actually is more than colleges attended as given in example, it also has courses, grades, addresses, ect... all on one line for each applicant.)

Thank you for the great answer and help!
-tony

Posted 13-May-14 11:02am

Member 10812903

Comments

Nelek 13-May-14 17:59pm

Please don't post solutions to chat with people asking or answering. The messages are not always sorted by date, so it can be a bit difficult to follow them correctly.
The best option is to use the "Have a question or comment?" (or the tiny "reply" on another comment). Another advantage is, that the person you write to will get a notification, otherwise it could be that he/she doesn't see your additional message.

In this case (for the future) the best you can do is what I am going to do for you. That is, to tell the one who answered you that you have updated the question, and then use the "improve question" widget on low right corner to add the information there.

Member 10812903 13-May-14 21:00pm

Nelek,
Sorry.. I naturally looked for a "reply" button, but could not find it within Jörgen answer.
I am new and will avoid doing that in future. Appreciate the site and do want to let the person who help me know that.

Nelek 14-May-14 4:34am

I guessed that, that's why I did it for you :)
Welcome and have fun in CP

Member 10812903 14-May-14 9:21am

Spot on... thanks for the kindness. Impressed with CP.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2014-05-12T11:12:00

Your tags claim that this is about SQL-Server-2014 while your text claims that you're using MS Access.
If I assume that your tags are correct I'd tell you use Unpivot[^].

But if you indeed are using Access there is no Unpivot available. So then you have (atleast) four choices. And I don't think you'll like any of them.

Either you can load the file to a staging table and unpivot manually:

SQL

Select  Applicant,School0 as school from stagingtable
union all
select  Applicant,school1 as school from stagingtable
union all
select  Applicant,school2 as school from stagingtable
union all
select  Applicant,school3 as school from stagingtable
... repeat 1300 times

Or you can install a real database and use unpivot.
Or use a real database as a staging area where you unpivot, then export the data to your access database.
Or you unpivot the data in a program before importing to the Access database.

<edit>Trying to answer your followup question.
The problem is that there is a maximum of 1024 columns in a table. But you can make inserts and queries with up to 4096 columns.
So there are again several choices to make, and one of them you have already seen yourself.

Import the file into three different tables having the same primary key, join the tables together into a subquery or a view where you do the pivot instead.
Import the file into a Wide Table[^]. The problem with wide Tables is that they rely on Column Sets and when you query a column set you get the result as XML.
Use a Fast CSV Reader[^] to get the data rows into a program where you split up and insert the data into the appropriate dbtable.
Combine 1 and 3.

</edit>