?
Solved

Exporting .csv to SQL DB

Posted on 2013-06-24
6
Medium Priority
?
590 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
6 Comments
 
LVL 2

Accepted Solution

by:
Agneau earned 2000 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
Quick Start: DOCKER

Sometimes you just need a Quick Start on a topic in order to begin using it.. this is just what you need to know to get up and running with Docker!

 

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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

719 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