Solved BULK IMPORT CSV file to SQL Server

[drifter96]

Hi, I am trying to import a CSV file with 287,000+ records into a SQL Server linked table. Using INSERT INTO takes a little over an hour. Someone had mentioned that there is a BULK INSERT that does the whole file at one time as apposed to one record at a time and should import a lot faster.

My google searching hasn't provided anything that works. Can someone point me in the right direction?

TIA
Jeff

 

[Ranman256]

did you use: Docmd.TransferText....? did that take an hour?

 

[theDBguy]

Try searching for BCP tool.

 

[CJ_London]

How are you using insert into? i.e. what is the full sql

 

[drifter96]

did you use: Docmd.TransferText....? did that take an hour?

Yes, that's what I was using first. Then Tried INSERT INTO.

 

[drifter96]

How are you using insert into? i.e. what is the full sql

I was trying it from a linked Excel Spreadsheet.

    strSQL = "INSERT INTO dbo_ESC_tblAllDeedOfTrustSecuredLoans ( [Institution Code], [Institution Abbreviation], [Account Number], [Application Number], [Source Application Code], " & _
    "[Account Status Description], Origination_Date, [Current Principal Balance Amount], [Insurance Policy Coverage Amount], [Coverage Amount], Borrower_Name, Coborrower_Name, " & _
    "[Mailing Address], [Mailing Address Line 2], [Mailing Address State Abbreviation], [Mailing Address City Name], [Mailing Address Base Zip Code], Property_Address_Line_1, " & _
    "Property_Address_Line_2, Property_Address_City, Property_Address_State, Property_Address_Zip_Code, [Residential Address], [Residential Address Line 2], " & _
    "[Residential Address State Abbreviation], [Residential Address City], [Residential Address Base Zip Code], [Property Street Address], [Property City], " & _
    "[Property State], [Property Zip Code], [Product Code], [Escrow Type Code], [Escrow Type Description], [Escrow Type Category], [Sequence Number], [Loan Payoff Amount], [Date] ) " & _
    "SELECT [All Deed of Trust Secured Loans].[Institution Code], [All Deed of Trust Secured Loans].[Institution Abbreviation], [All Deed of Trust Secured Loans].[Account Number], " & _
    "[All Deed of Trust Secured Loans].[Application Number], [All Deed of Trust Secured Loans].[Source Application Code], [All Deed of Trust Secured Loans].[Account Status Description], " & _
    "[All Deed of Trust Secured Loans].Origination_Date, [All Deed of Trust Secured Loans].[Current Principal Balance Amount], [All Deed of Trust Secured Loans].[Insurance Policy Coverage Amount], " & _
    "[All Deed of Trust Secured Loans].[Coverage Amount], [All Deed of Trust Secured Loans].Borrower_Name, [All Deed of Trust Secured Loans].Coborrower_Name, " & _
    "[All Deed of Trust Secured Loans].[Mailing Address], [All Deed of Trust Secured Loans].[Mailing Address Line 2], [All Deed of Trust Secured Loans].[Mailing Address State Abbreviation], " & _
    "[All Deed of Trust Secured Loans].[Mailing Address City Name], [All Deed of Trust Secured Loans].[Mailing Address Base Zip Code], [All Deed of Trust Secured Loans].Property_Address_Line_1, " & _
    "[All Deed of Trust Secured Loans].Property_Address_Line_2, [All Deed of Trust Secured Loans].Property_Address_City, [All Deed of Trust Secured Loans].Property_Address_State, " & _
    "[All Deed of Trust Secured Loans].Property_Address_Zip_Code, [All Deed of Trust Secured Loans].[Residential Address], [All Deed of Trust Secured Loans].[Residential Address Line 2], " & _
    "[All Deed of Trust Secured Loans].[Residential Address State Abbreviation], [All Deed of Trust Secured Loans].[Residential Address City], " & _
    "[All Deed of Trust Secured Loans].[Residential Address Base Zip Code], [All Deed of Trust Secured Loans].[Property Street Address], [All Deed of Trust Secured Loans].[Property City], " & _
    "[All Deed of Trust Secured Loans].[Property State], [All Deed of Trust Secured Loans].[Property Zip Code], [All Deed of Trust Secured Loans].[Product Code], " & _
    "[All Deed of Trust Secured Loans].[Escrow Type Code], [All Deed of Trust Secured Loans].[Escrow Type Description], [All Deed of Trust Secured Loans].[Escrow Type Category], " & _
    "[All Deed of Trust Secured Loans].[Sequence Number] , [All Deed of Trust Secured Loans].[Loan Payoff Amount], [All Deed of Trust Secured Loans].Date " & _
    "FROM [All Deed of Trust Secured Loans]; "
    DoCmd.RunSQL strSQL

 

[CJ_London]

as an alternative to using transferspreadsheet try replacing

"FROM [All Deed of Trust Secured Loans]; "

with

"FROM
 (SELECT * FROM [sheet1$A:D] AS xlData IN 'C:\Path\filename.XLSX'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes])  AS  [All Deed of Trust Secured Loans]; "

replace sheet1$:A and C:\Path\filename with the appropriate values.

which may speed up the process - if only the time it takes to create the linked file

note that you do not need to include the tablename in the select part of the sql which reduces some typing and improves readability

I would also recommend using currentdb.execute strSQL, dbfailonerror instead of docmd.runsql strSQL

 

[drifter96]

as an alternative to using transferspreadsheet try replacing

"FROM [All Deed of Trust Secured Loans]; "

with

"FROM
(SELECT * FROM [sheet1$A:D] AS xlData IN 'C:\Path\filename.XLSX'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes])  AS  [All Deed of Trust Secured Loans]; "

replace sheet1$:A and C:\Path\filename with the appropriate values.

which may speed up the process - if only the time it takes to create the linked file

note that you do not need to include the tablename in the select part of the sql which reduces some typing and improves readability

I would also recommend using currentdb.execute strSQL, dbfailonerror instead of docmd.runsql strSQL

I tried making the changes and get the following error: An Error Occurred: 3134 - Syntax error in INSERT INTO Statement.

    strSQL = "INSERT INTO dbo_ESC_tblAllDeedOfTrustSecuredLoans ( [Institution Code], [Institution Abbreviation], [Account Number], [Application Number], [Source Application Code], " & _
    "[Account Status Description], Origination_Date, [Current Principal Balance Amount], [Insurance Policy Coverage Amount], [Coverage Amount], Borrower_Name, Coborrower_Name, " & _
    "[Mailing Address], [Mailing Address Line 2], [Mailing Address State Abbreviation], [Mailing Address City Name], [Mailing Address Base Zip Code], Property_Address_Line_1, " & _
    "Property_Address_Line_2, Property_Address_City, Property_Address_State, Property_Address_Zip_Code, [Residential Address], [Residential Address Line 2], " & _
    "[Residential Address State Abbreviation], [Residential Address City], [Residential Address Base Zip Code], [Property Street Address], [Property City], " & _
    "[Property State], [Property Zip Code], [Product Code], [Escrow Type Code], [Escrow Type Description], [Escrow Type Category], [Sequence Number], [Loan Payoff Amount], [Date] ) " & _
    "FROM (SELECT * FROM [All_Deed_of_Trust_Secured_Loans$A:AL] AS xlData IN 'R:\Loan Administration\Loan Servicing\Escrow Department\Flood Insurance\Flood Vendors\ServiceLink\Flood Revision Notice Reports\All_Deed_of_Trust_Secured_Loans.xlsx'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes])  AS  [All Deed of Trust Secured Loans]; "
    CurrentDb.Execute strSQL, dbFailOnError

 

[CJ_London]

you've left out

"SELECT [All Deed of Trust Secured Loans].[Institution Code], [All Deed of Trust Secured Loans].[Institution Abbreviation], [All Deed of Trust Secured Loans].[Account Number], " & _
    "[All Deed of Trust Secured Loans].[Application Number], [All Deed of Trust Secured Loans].[Source Application Code], [All Deed of Trust Secured Loans].[Account Status Description], " & _
    "[All Deed of Trust Secured Loans].Origination_Date, [All Deed of Trust Secured Loans].[Current Principal Balance Amount], [All Deed of Trust Secured Loans].[Insurance Policy Coverage Amount], " & _
    "[All Deed of Trust Secured Loans].[Coverage Amount], [All Deed of Trust Secured Loans].Borrower_Name, [All Deed of Trust Secured Loans].Coborrower_Name, " & _
    "[All Deed of Trust Secured Loans].[Mailing Address], [All Deed of Trust Secured Loans].[Mailing Address Line 2], [All Deed of Trust Secured Loans].[Mailing Address State Abbreviation], " & _
    "[All Deed of Trust Secured Loans].[Mailing Address City Name], [All Deed of Trust Secured Loans].[Mailing Address Base Zip Code], [All Deed of Trust Secured Loans].Property_Address_Line_1, " & _
    "[All Deed of Trust Secured Loans].Property_Address_Line_2, [All Deed of Trust Secured Loans].Property_Address_City, [All Deed of Trust Secured Loans].Property_Address_State, " & _
    "[All Deed of Trust Secured Loans].Property_Address_Zip_Code, [All Deed of Trust Secured Loans].[Residential Address], [All Deed of Trust Secured Loans].[Residential Address Line 2], " & _
    "[All Deed of Trust Secured Loans].[Residential Address State Abbreviation], [All Deed of Trust Secured Loans].[Residential Address City], " & _
    "[All Deed of Trust Secured Loans].[Residential Address Base Zip Code], [All Deed of Trust Secured Loans].[Property Street Address], [All Deed of Trust Secured Loans].[Property City], " & _
    "[All Deed of Trust Secured Loans].[Property State], [All Deed of Trust Secured Loans].[Property Zip Code], [All Deed of Trust Secured Loans].[Product Code], " & _
    "[All Deed of Trust Secured Loans].[Escrow Type Code], [All Deed of Trust Secured Loans].[Escrow Type Description], [All Deed of Trust Secured Loans].[Escrow Type Category], " & _
    "[All Deed of Trust Secured Loans].[Sequence Number] , [All Deed of Trust Secured Loans].[Loan Payoff Amount], [All Deed of Trust Secured Loans].Date " & _

I was suggesting you could remove [All Deed of Trust Secured Loans].

eg from

"SELECT [All Deed of Trust Secured Loans].[Institution Code], [All Deed of Trust Secured Loans].[Institution Abbreviation], [All Deed of Trust Secured Loans].[Account Number], " etc

to leave

"SELECT [Institution Code], [Institution Abbreviation], [Account Number], " etc

you could try changing

"FROM (SELECT * FROM [All_Deed_of_Trust_Secured_Loans$A:AL] AS xlData IN 'R:\Loan Administration\Loan Servicing\Escrow Department\Flood Insurance\Flood Vendors\ServiceLink\Flood Revision Notice Reports\All_Deed_of_Trust_Secured_Loans.xlsx'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes])  AS  [All Deed of Trust Secured Loans]; "

to

"SELECT * FROM (SELECT * FROM [All_Deed_of_Trust_Secured_Loans$A:AL] AS xlData IN 'R:\Loan Administration\Loan Servicing\Escrow Department\Flood Insurance\Flood Vendors\ServiceLink\Flood Revision Notice Reports\All_Deed_of_Trust_Secured_Loans.xlsx'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes])  AS  [All Deed of Trust Secured Loans]; "

or remove the brackets and alias - I use them so you can see the table in the QBE which is usually neccessary if you need to join to other tables.

"SELECT * FROM [All_Deed_of_Trust_Secured_Loans$A:AL] AS xlData IN 'R:\Loan Administration\Loan Servicing\Escrow Department\Flood Insurance\Flood Vendors\ServiceLink\Flood Revision Notice Reports\All_Deed_of_Trust_Secured_Loans.xlsx'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes]"

but this does rely on your fields being in the same order and the same number of fields

If struggling, create an new select query with this sql (note quotes removes so you should just copy/paste

SELECT * FROM (SELECT * FROM [All_Deed_of_Trust_Secured_Loans$A:AL] AS xlData IN 'R:\Loan Administration\Loan Servicing\Escrow Department\Flood Insurance\Flood Vendors\ServiceLink\Flood Revision Notice Reports\All_Deed_of_Trust_Secured_Loans.xlsx'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes])  AS  ImportData

run it to check you get your values, then convert it to an append query.

If your fields match by column order and the destination table does not have additional fields (such as a PK) then you should be able to use

strSQL = "INSERT INTO dbo_ESC_tblAllDeedOfTrustSecuredLoans " & _
"SELECT * FROM (SELECT * FROM [All_Deed_of_Trust_Secured_Loans$A:AL] AS xlData IN 'R:\Loan Administration\Loan Servicing\Escrow Department\Flood Insurance\Flood Vendors\ServiceLink\Flood Revision Notice Reports\All_Deed_of_Trust_Secured_Loans.xlsx'[Excel 12.0;HDR=Yes;IMEX=0;ACCDB=Yes])"
currentdb.execute strSQL,dbfailonerror