Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 594
  • Last Modified:

Exporting .csv to SQL DB

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
chokka
Asked:
chokka
  • 5
1 Solution
 
AgneauCommented:
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
 
chokkaAuthor Commented:
Thank you Agneau for your help !! Let me test the code and respond back to you.
0
 
chokkaAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
chokkaAuthor Commented:
Agneau, it works in the Excel.

Great , Thank you so much.

I will find a way to work it
0
 
chokkaAuthor Commented:
Thank you !!
0
 
chokkaAuthor Commented:
@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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now