Recent Posts
Tags
 

Better CSV Export from MicroSoft Excel for MySQL

Better CSV Export from MicroSoft Excel for MySQL

12:54 25 August in General
0 Comments

I had a problem getting a CSV from Excel in a format that would easily import into a MySQL database.  So I looked around, and found this macro. (I don’t recall the source, or I’d post that too…)

Sub CSVFile()
Dim SrcRg As Range
Dim CurrRow As Range
Dim CurrCell As Range
Dim CurrTextStr As String
Dim ListSep As String
Dim FName As Variant
FName = Application.GetSaveAsFilename(“”, “CSV File (*.csv), *.csv”)
If FName <> False Then
ListSep = Application.International(xlListSeparator)
If Selection.Cells.Count > 1 Then
Set SrcRg = Selection
Else
Set SrcRg = ActiveSheet.UsedRange
End If
Open FName For Output As #1
For Each CurrRow In SrcRg.Rows
CurrTextStr = “”
For Each CurrCell In CurrRow.Cells
CurrTextStr = CurrTextStr & “””” & CurrCell.Value & “””” & ListSep
Next
While Right(CurrTextStr, 1) = ListSep
CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) – 1)
Wend
Print #1, CurrTextStr
Next
Close #1
End If
End Sub

Sub CSVFile()

Dim SrcRg As Range

Dim CurrRow As Range

Dim CurrCell As Range

Dim CurrTextStr As String

Dim ListSep As String

Dim FName As Variant

FName = Application.GetSaveAsFilename(“”, “CSV File (*.csv), *.csv”)

If FName <> False Then

ListSep = Application.International(xlListSeparator)

If Selection.Cells.Count > 1 Then

Set SrcRg = Selection

Else

Set SrcRg = ActiveSheet.UsedRange

End If

Open FName For Output As #1

For Each CurrRow In SrcRg.Rows

CurrTextStr = “”

For Each CurrCell In CurrRow.Cells

CurrTextStr = CurrTextStr & “””” & CurrCell.Value & “””” & ListSep

Next

While Right(CurrTextStr, 1) = ListSep

CurrTextStr = Left(CurrTextStr, Len(CurrTextStr) – 1)

Wend

Print #1, CurrTextStr

Next

Close #1

End If

End Sub

No Comments

Post A Comment

%d bloggers like this: