I want to create an access table, and insert rows using an excel spreadsheet programmatically basically a copy and paste from excel to access table.

Question

1.00/5 (1 vote)

See more:

Guys i have literally tried so much research my head is about to explode. can someone please help me in this matter. I have multiple excel spreadsheets with 100000 plus rows and 27+ columns. i want to dynamically create tables, and columns and insert the excel into the access db. Is there any code example i can use. or has someone worked with this that i can use please? thank you in advance.

What I have tried:

Way too much research leading to nothing.

Posted 3-Mar-17 5:19am

JT1992

Updated 3-Mar-17 5:29am

v2

Add a Solution

Comments

CHill60 3-Mar-17 11:23am

I'm afraid this is not a code-writing service. Do you know how to open an excel spreadsheet? Do you know how to connect to Access? What is your actual problem (that could warrant a Quick Answer)?

JT1992 3-Mar-17 11:27am

yes i know how to open an excel spreadsheet but how can i connect to access and basically copy that excel spreadsheet create a table using the column names dynamically and then insert all the rows within each column. all row cells will be in a text format. i really need help sir anything will do.

[no name] 3-Mar-17 11:27am

"i need professional help and code project has amazing people", well yeah but no one is going to sit down and write this for you. Open your excel sheet, open your access database, query excel, write to access.

JT1992 3-Mar-17 11:28am

i dont know how to open access and do the querying.

[no name] 3-Mar-17 11:30am

Sorry? I could swear you said you researched this.

JT1992 3-Mar-17 11:32am

yes but nothing is helping me find a way to use the excel sheet columns as my column names to create a table or how to do that.

[no name] 3-Mar-17 11:34am

Okay so you need to make up your mind as to what your actual question is. The once you figure that out, research that.

JT1992 3-Mar-17 11:37am

thanks for the help. i understand you probably know the answer to my question but cut out the rudeness everyone learns from blogs and posts. and my question above does state that i need help creating a table with excel column names. but once again thanks for the help

[no name] 3-Mar-17 15:48pm

What rudeness are you talking about? You keep changing the "question"! Ever hear of "trying to hit a moving target"?
"i need help creating a table with excel column names", yeah after you asked "how do I do my entire project" and then you changed that to "i dont know how to open access and do the querying" then you change that to "i need help creating a table with excel column names" but of course you never say exactly what help you need. Learn how to ask questions then people trying to help you will seem less "rude".

JT1992 3-Mar-17 15:52pm

"Okay so you need to make up your mind as to what your actual question is. The once you figure that out, research that." if i needed to research that i would have done that without posting a question. i needed a suggestion and the person below who has been helping me below in the answer panel has done it without any rudeness. i am new to .net so haven't worked with any of these things before in my life. and pretty sure you don't learn a new technology with someone saying keep researching. i did that hit a deadspot thats why i came here for a suggestion.

[no name] 3-Mar-17 21:04pm

Well if you want to be offended by nothing then that is up to you. Doesn't bother me a bit. I wasn't rude to you at all in any way. If you don't know how to ask a question, that is not my problem. Being "new" to .NET has nothing at all to do with anything.

Rest assured, you do not have to worry about me being "rude" to you anymore. I will put you on my "makes up thing to be offended for" list and won't help you any further. Good luck.

1 solution

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)

OriginalGriff · Accepted Answer · 2017-03-03T05:29:00

Solution 1

You already know how to read the data: System out of memory exception when loading an excel spreadsheet with 100000+ rows with 25 columns or more to a gridview[^] - all you have to do is save the DataTable to Access - which is pretty trivial: save the DataTable to Access - Google Search[^]
Provided you don't try to display all that data, you'll be fine.

Posted 3-Mar-17 5:29am

OriginalGriff

Comments

JT1992 3-Mar-17 11:32am

i really dont know how to create different tables with different column names dynamically depending on spreadsheet. please help i am really lost.

OriginalGriff 3-Mar-17 11:38am

Why not? What part of the process is a problem?
Can you create a table in Access from your C#?

JT1992 3-Mar-17 11:39am

thats the issue i cant create a table dynamically. each spreadsheet will have different column names. or maybe i am thinking too much

OriginalGriff 3-Mar-17 11:53am

Maybe you are.
What are you trying to do - rather that thinking about the "mechanics" of a solution, perhaps a new set of eyes on the problem might help?

JT1992 3-Mar-17 11:56am

okay i am trying to read an excel spreadsheet, then create an access table with the same data. so basically read excel and write to access database table. each excel will have different column names and i would never know the exact name. however, i have to create a table each time i read a new excel spreadsheet. and hey thank you so much for the help. really appreciate.

OriginalGriff 3-Mar-17 12:10pm

So what's the problem?
You can read the spreadsheet into a DataTable - we know that - and that gives you the columns, their rows, and their datatypes.
So all you need to do is create an access table from the DataTable and give it a name. And the google link I gave above shows you how to do that.
Repeat those actions for each spreadsheet and you should be done.
So it should be a fairly simple cut'n'shut job, shouldn't it?

JT1992 3-Mar-17 12:12pm

but how would i be able to create the column names. i know you can create it in the create table statement. but how would i know what each column name will be.

WRX17 3-Mar-17 12:21pm

Have you tried SQL ALTER statements?

Make the dynamic table with a single temporary column or just the unique table key.
After that use a SQL ALTER statement to add the columns from the excel sheet.
https://msdn.microsoft.com/en-us/library/bb177883(v=office.12).aspx

OriginalGriff 3-Mar-17 12:21pm

Have you looked at the DataColumn.ColumnName property? And the DataColumn.DataType property?

JT1992 7-Mar-17 10:12am

hey thanks took a while to figure out but i created a dataset and paging to show 1k records max at a time. its faster and more efficient.

OriginalGriff 7-Mar-17 10:29am

You're welcome!