Solved

Exporting .csv to SQL DB

Posted on 2013-06-24
6
570 Views
Last Modified: 2013-06-25
I have to import .csv file to my sql database. CSV file is not in a proper format.

So. i manually change the format to an Excel spreadsheet and then import to sql database.

Now, i am working on a tool which will import csv file to database and do our necessary needs.

I have attached the sample format of csv and converted format of excel ( which i am doing it manually ). I request experts and throw their suggestion for importing a csv file to sql database.
Test-6619289--5-30-2013.csv
Test-6619289--5-30-2013.xls
0
Comment
Question by:chokka
  • 5
6 Comments
 
LVL 2

Accepted Solution

by:
Agneau earned 500 total points
ID: 39272264
Hello chokka,

I'm assuming that each file contain one invoice and each invoice can have several ships

My example attached implements a VBA code that parses your csv file into two user defined types (InvoiceHeader and InvoiceDetail).

There is also a procedure (InsertRecord) that can be modified just to insert the records already parsed into your SQL table. I'm also assuming that you know how to connect a SQL database and properly insert records into a table, otherwise let me know so I can provide you with a sample code to do it.



Option Explicit

Type InvoiceHeader
    Invoice_Date As Date
    Invoice_Number As Long
    Account_Number As Long
    DEA_Number As String
    Sales_Rep As Long
End Type

Type InvoiceDetail
    Ship_Qty As Long
    Order_Qty As Long
    Code As Variant
    UM As String
    Blank1 As String
    Description As String
    Blank2 As String
    Blank3 As String
    Blank4 As String
    CIN As Long
    PO_Number As String
    Due_Date As Date
    DEA_Class As Long
    Unit_Price As Double
    Extension As Double
End Type

Dim objFSO As Scripting.FileSystemObject
Dim FileTextStream As Scripting.TextStream

Sub MyCaller()
    Dim objDialog As Object
    Dim intResult As Integer
    
    Set objDialog = CreateObject("UserAccounts.CommonDialog")
    objDialog.Filter = "Text Files|*.csv|All Files|*.*"
    objDialog.FilterIndex = 1
    objDialog.InitialDir = "C:\"
    intResult = objDialog.ShowOpen

    If intResult = 0 Then
        Exit Sub
    End If
    
    Call ImportCSV(objDialog.FileName)
End Sub

Sub ImportCSV(FileName As String)
    Dim strInvoiceDetail As String
    Dim CurrentInvoiceHeader As InvoiceHeader
    Dim CurrentInvoiceDetail As InvoiceDetail
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    If objFSO.FileExists(FileName) Then
        Set FileTextStream = objFSO.OpenTextFile(FileName, ForReading)
        If Not FileTextStream.AtEndOfStream Then
            CurrentInvoiceHeader = GetInvoiceHeader()
            While FileTextStream.ReadLine <> "Ship Qty,Order Qty,Code,UM,,Description,,,,CIN,PO Number,Due Date,DEA Class,Unit Price,Extension"
            Wend
            strInvoiceDetail = FileTextStream.ReadLine
            While Left(strInvoiceDetail, 13) <> "Report Total:"
                CurrentInvoiceDetail = GetInvoiceDetail(strInvoiceDetail)
                Call InsertRecord(CurrentInvoiceHeader, CurrentInvoiceDetail)
                strInvoiceDetail = FileTextStream.ReadLine
            Wend
        End If
    Else
        MsgBox "File " & FileName & " does not exist", vbOKOnly + vbCritical, "Error"
    End If
    
End Sub

Private Function GetInvoiceHeader() As InvoiceHeader
    Dim HeaderFields() As String
    
    While FileTextStream.ReadLine <> "Invoice Date,Invoice Number,Account Number,DEA Number,Sales Rep,,,,,,,,,,"
    Wend

    HeaderFields = Split(FileTextStream.ReadLine, ",")
    
    GetInvoiceHeader.Invoice_Date = CDate(HeaderFields(0))
    GetInvoiceHeader.Invoice_Number = CLng(HeaderFields(1))
    GetInvoiceHeader.Account_Number = CLng(HeaderFields(2))
    GetInvoiceHeader.DEA_Number = HeaderFields(3)
    GetInvoiceHeader.Sales_Rep = CLng(HeaderFields(4))

End Function

Private Function GetInvoiceDetail(strRecord As String) As InvoiceDetail
    Dim DetailFields() As String
    
    DetailFields = Split(strRecord, ",")
    
    GetInvoiceDetail.Ship_Qty = CLng(DetailFields(0))
    GetInvoiceDetail.Order_Qty = CLng(DetailFields(1))
    GetInvoiceDetail.Code = DetailFields(2)
    GetInvoiceDetail.UM = DetailFields(3)
    GetInvoiceDetail.Blank1 = DetailFields(4)
    GetInvoiceDetail.Description = DetailFields(5)
    GetInvoiceDetail.Blank2 = DetailFields(6)
    GetInvoiceDetail.Blank3 = DetailFields(7)
    GetInvoiceDetail.Blank4 = DetailFields(8)
    GetInvoiceDetail.CIN = CLng(DetailFields(9))
    GetInvoiceDetail.PO_Number = DetailFields(10)
    GetInvoiceDetail.Due_Date = CDate(DetailFields(11))
    GetInvoiceDetail.DEA_Class = CLng(DetailFields(12))
    GetInvoiceDetail.Unit_Price = CDbl(DetailFields(13))
    GetInvoiceDetail.Extension = CDbl(DetailFields(14))

End Function

Private Sub InsertRecord(invHeader As InvoiceHeader, invDetail As InvoiceDetail)
    Dim lngRow As Long
        
    lngRow = Sheet1.Cells(1, 1).CurrentRegion.Rows.Count + 1
        
    Sheet1.Cells(lngRow, 1) = invDetail.Ship_Qty
    Sheet1.Cells(lngRow, 2) = invDetail.Order_Qty
    Sheet1.Cells(lngRow, 3) = invDetail.Code
    Sheet1.Cells(lngRow, 4) = invDetail.UM
    Sheet1.Cells(lngRow, 5) = invDetail.Blank1
    Sheet1.Cells(lngRow, 6) = invDetail.Description
    Sheet1.Cells(lngRow, 7) = invDetail.Blank2
    Sheet1.Cells(lngRow, 8) = invDetail.Blank3
    Sheet1.Cells(lngRow, 9) = invDetail.Blank4
    Sheet1.Cells(lngRow, 10) = invDetail.CIN
    Sheet1.Cells(lngRow, 11) = invDetail.PO_Number
    Sheet1.Cells(lngRow, 12) = invDetail.Due_Date
    Sheet1.Cells(lngRow, 13) = invDetail.DEA_Class
    Sheet1.Cells(lngRow, 14) = invDetail.Unit_Price
    Sheet1.Cells(lngRow, 15) = invDetail.Extension
    Sheet1.Cells(lngRow, 16) = invHeader.Invoice_Date
    Sheet1.Cells(lngRow, 17) = invHeader.Invoice_Number
    Sheet1.Cells(lngRow, 18) = invHeader.Account_Number
    Sheet1.Cells(lngRow, 19) = invHeader.DEA_Number
    Sheet1.Cells(lngRow, 20) = invHeader.Sales_Rep
        
End Sub

Open in new window

ImportCSV.xls
0
 

Author Comment

by:chokka
ID: 39272431
Thank you Agneau for your help !! Let me test the code and respond back to you.
0
 

Author Comment

by:chokka
ID: 39272521
Agneau, can you please give me the code in VB.Net or C#.Net ?

Although, i copied the code and fixed the syntax to the best i can in VB.Net Environment.

I couldn't find matching dll's for   Scripting.FileSystemObject

 Dim objFSO As Scripting.FileSystemObject
    Dim FileTextStream As Scripting.TextStream
0
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 

Author Comment

by:chokka
ID: 39272605
Agneau, it works in the Excel.

Great , Thank you so much.

I will find a way to work it
0
 

Author Closing Comment

by:chokka
ID: 39272614
Thank you !!
0
 

Author Comment

by:chokka
ID: 39276432
@Agneau, Thank you for your help.

I have a small issue with Split(strRecord, ",")

You helped to split the record by comma and to insert into an excel workbook.

For Currency such as $1,000.10  - SplitRecord function for comma is throwing an exception.

How should i handle this issue ??


Private Function GetInvoiceDetail(strRecord As String) As InvoiceDetail
    Dim DetailFields() As String
    
    DetailFields = Split(strRecord, ",")
    
    GetInvoiceDetail.Ship_Qty = CLng(DetailFields(0))
    GetInvoiceDetail.Order_Qty = CLng(DetailFields(1))
    GetInvoiceDetail.Code = DetailFields(2)
    GetInvoiceDetail.UM = DetailFields(3)
    GetInvoiceDetail.Blank1 = DetailFields(4)
    GetInvoiceDetail.Description = DetailFields(5)
    GetInvoiceDetail.Blank2 = DetailFields(6)
    GetInvoiceDetail.Blank3 = DetailFields(7)
    GetInvoiceDetail.Blank4 = DetailFields(8)
    GetInvoiceDetail.CIN = CLng(DetailFields(9))
    GetInvoiceDetail.PO_Number = DetailFields(10)
    GetInvoiceDetail.Due_Date = CDate(DetailFields(11))
    GetInvoiceDetail.DEA_Class = CLng(DetailFields(12))
    GetInvoiceDetail.Unit_Price = CDbl(DetailFields(13))
    GetInvoiceDetail.Extension = CDbl(DetailFields(14))

End Function

Open in new window

Test-6619289--5-30-2013.csv
ImportCSV.xls
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question