Import Excel File to SQL Database
-
Hi All,
do you guys have the VB coding to import an excel file into the SQL database? please.. its urgent..
thanks a million!
[font="Verdana"]reubenfoo:D
email: reubenfoo89@hotmail.com[/font] -
Is this a one-time thing or on-going? You should look into SSIS as that is what it is for.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
its like,
Step 1: A user selects an excel file
Step 2: The excel file is being imported in to the database
Step 3: The contents in the file is being extracted for viewing and editing
Step 4: Once confirmed, the output file would be in text format.
[font="Verdana"]reubenfoo:D
email: reubenfoo89@hotmail.com[/font] -
Steps 1 through 3 can can be very easily accomplished... open the file in Excel. Step 4 is a simple "Save As". You don't need to even go near the database for such a thing. đŸ˜‰
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
It's required as all the data must be saved into the database and from there, extracted out.
[font="Verdana"]reubenfoo:D
email: reubenfoo89@hotmail.com[/font] -
Why you would want to do this in VB and SQL Server is beyond me. Like Jeff says, you don't need to go anywhere near the database for the functionality you've described.
Maybe what you've told us is only the tip of the iceberg. At present I send no need for either VB or SQL Server. If you'd like to elaborate a little more on how your process is supposed to work, we might see the reason for the path you've chosen.
--------------------
Colt 45 - the original point and click interface
-
I am not very comfirtable with your requirement. There has to be some proper logic behind.
Anyway, you need to know the features and properties of Excel.Application object in VB for coding purpose. Also it depends on the data structure of your Excel file as will it be same ot may vary. What about empty cell... lots of things are involved before one can suggest something good in your case.
Better look the MS Access for your need as that will be easy for coding and handling as well.
Regards
Atul
-
Most of this could be done with OpenRowset. That, at least, can select from Excel into SQL. Given dynamic SQL and an input parameter of the file name and location, it could get you at least that far.
What you would need to do at that point is check that the Excel file has the right columns for the table you want to import it into, or use the columns to create a new table (Select Into). Alternately, you can query the Excel file to find what columns it does have, and build a user interface to map those columns to the target table.
In order to do this, you'll need to make sure you have the right settings for your OLE DB/ODBC connection to Excel, since it can otherwise lose data. Search MSDN for "IMEX", you'll find information and directions on that.
The above can be very, very simple if the spreadsheets follow an exact format with complete data consistency. If not, it can be quite complex.
The analysis of the data will be up to you, since that's going to depend on what the data is and what you want to do with it.
Output to text files can be easily handled by using BCP.
SSIS is also an option for several of these steps. Again, make sure to look up IMEX on MSDN before you get too far into this option, since data can otherwise be lost.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
-
philcart (12/3/2008)
Why you would want to do this in VB and SQL Server is beyond me. Like Jeff says, you don't need to go anywhere near the database for the functionality you've described.Reading the additional explanation given, it's because they're using the database as an archive to show what was done in the spreadsheets and it get's around them having to write a front end.
I think a macro in the spreadsheet would be the best way to accomplish this... could be made to do the update through a "pass through" view or two.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
whats the coding to put an excel file data into a MS access table?
The table name would be "apciti"
The field names are... PMCO, PMLOC, PMIPMT, PMEPMT, PMEDOC, PMCURI, PYORPM, PYDATE, PMVNCD, VENNAM, VADR1, VADR2, VADR3, PMORPT, PMPMTT, PMPSLN, PMDOCT, PMACDT, PMCRDT, PMCRTT, PMCROP. Primary Key would be APCITID - auto gen with increment of 1
All contents are in the excel file.
SOrry for the changes.. The user suddenly changed the requirements
[font="Verdana"]reubenfoo:D
email: reubenfoo89@hotmail.com[/font] -
reubenfoo89 (12/2/2008)
Hi All,do you guys have the VB coding to import an excel file into the SQL database? please.. its urgent..
thanks a million!
This is an example only
Excel file : excelfile
userid username
database table : dbTable
userid username
1. create a sql db connection. [con]
2. create a recordset. [rs]
3. create a excel file connection [xlcon] ['Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:\Source\Addresses.xls']
4. create excel file recordset [xlrs]
5. open the excel file as
xlrs.open "'SELECT * FROM [Sheet1$]", xlcon
6. create a while or for loop to insert the data
rs!userid = xlrs!userid
rs!username = xlrs!username
rs.update
use this method if you wanna apply some formatting on the data. [A very slow process. but quite accurate]
7. for speed, use the query. [Insert into dbTable select * from excelfile]
kshitij kumar
kshitij@krayknot.com
www.krayknot.com -
You can do this with a macro VBA code in Excel:)
This is a simplified! code there a user within a company network
or outside if the database is accessible can update a table.
Don't use this in a multiuser environment.
One insert per row. It is possible to make on insert per "sheet"
easy in SQL server 2008.
Beware of empty cells and of the formates.
The example uses stringdata
"Set Ref to ActiveX Data Objects library
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim K as long
conn.Provider = "sqloledb"
conn.Properties("Network Library").Value = "DBMSSOCN"
source = "Data source=yourhost;User id=yyyyy;Password=xxxxx; Initial Catalog=Database"
conn.Open source
‘This insert row 1 to 100 column 1 to 8 in the active sheet
‘String values.
'If empty tableexample before insert
cmd.CommandText = "Truncate table tableexample"
cmd.execute
For K = 1 to K = 100
cmd.CommandText = "insert into tableexample values " & _
"('" & Cells(K, 1).Value & "','" & Cells(K, 2).Value & "','" & _
Cells(K, 3).Value & "','" & Cells(K, 4).Value & "','" & _
Cells(K, 5).Value & "','" & Cells(K, 6).Value & "','" & _
Cells(K, 7).Value & "','" & Cells(K, 8).Value & ")
cmd.Execute
Next K
Hopefully something to work on.
//Gosta
-
Hi Reubenfoo,
After looking your information it is hard to say if all the Excel file will have the same kind of data or same column structure. If it is same, it is possible to use the VBA code, else it is very difficult to process the data properly. If you want each Excel file to represent separate tables, it is easy to do. If you want all files to update single table, the VBA coding will be complex to handle them for validation purpose.
Thanks
Atul
-
"whats the coding to put an excel file data into a MS access table?
The table name would be "apciti"
The field names are... PMCO, PMLOC, PMIPMT, PMEPMT, PMEDOC, PMCURI, PYORPM, PYDATE, PMVNCD, VENNAM, VADR1, VADR2, VADR3, PMORPT, PMPMTT, PMPSLN, PMDOCT, PMACDT, PMCRDT, PMCRTT, PMCROP. Primary Key would be APCITID - auto gen with increment of 1
All contents are in the excel file.
SOrry for the changes.. The user suddenly changed the requirements
reubenfoo
email: reubenfoo89@hotmail.com "
reubenfoo.
Your information is not complete. Do you still want to use VB or
VBA?
Primary Key would be APCITID - auto gen with increment of 1 ?
Is there data already in the table?
My small example can be modified for Access and you can add the increment function.
as you already have the counter K.
//Gosta
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply