Solved

Exporting .csv to SQL DB

Posted on 2013-06-24
6
559 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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 will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

776 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