CSV Export and Import

M

ManvinderKaur

Board Regular
Joined
Jun 16, 2010
Messages
149
Hi I am a command button for exporting data as csv file and Importting data from csv file. I have a problem while exportting and importing.
I have range E43 that have drop down values containing comma value for example "Fishing, Mining company" when I iport this data instead of going into one cell that goes two separate cells. Hoe to fix this?

export code
Private Sub cmdSendBaselineData_Click()
Dim sPath As Variant
Dim sOutput As String
Dim csvSendData As VbMsgBoxResult
On Error GoTo errHandler

'call for funtion Validate data for data validation
If ValidateDataBaseline = False Then
Exit Sub
End If

GetFileName:
'get Path to save file as csv
sPath = Application.GetSaveAsFilename(ActiveWorkbook.Path & "\" & "Baseline" & Format(Now, "yyyymmddhmmss") & ".csv", FileFilter:="Excel Files (*.csv), *.csv", Title:="Save As")
If sPath = False Then
csvSendData = MsgBox("No csv file created", vbInformation)
Exit Sub
End If
If Dir(sPath) <> "" Then
ans = MsgBox("File already exists, you can not overwrite existing file?", vbRetryCancel)
If ans = vbRetry Then
GoTo GetFileName
ElseIf ans = vbCancel Then
Exit Sub
End If
End If

'build the output string
sOutput = Sheets("1.0 Business Details").Range("E12").Value & "-" & Sheets("1.0 Business Details").Range("E14").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E43").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E45").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("A42").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("A43").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E13").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N12").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E15").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N15").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E20").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E21").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E22").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("I22").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N20").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N21").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N22").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("R22").Value & ","
sOutput = sOutput & Sheets("2.0 Baseline Summary").Range("E11").Value & ","
sOutput = sOutput & Sheets("2.0 Baseline Summary").Range("E12").Value & ","
sOutput = sOutput & Sheets("2.0 Baseline Summary").Range("I12").Value & ","
sOutput = sOutput & Format(Sheets("1.0 Business Details").Range("E32").Value, "mm-dd-yyyy") & ","
sOutput = sOutput & Format(Sheets("1.0 Business Details").Range("E33").Value, "mm-dd-yyyy") & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N13").Value & ","
sOutput = sOutput & Sheets("2.1 Production Efficiency").Range("E8").Value & ","
'output to csv file
Open sPath For Append As #1
Print #1, sOutput
Close #1
csvSendData = MsgBox("A csv file has been created at" & _
vbCrLf & sPath & vbCrLf & "Please send this file as an email attachment to " & _
vbCrLf & "abc@.au", vbInformation + vbOKOnly, "Baseline Data")
errExit:
Close #1
Exit Sub
errHandler:
If Err.Number > 0 And Err.Number <> 75 Then MsgBox Err.Number & Err.Description
If Err.Number = 75 Then MsgBox ("You do not have specific permissions to create a file.")
Resume errExit
End Sub


Import code

'Import Baseline data from csv file
'Created by : Manvinder Kaur (Dialog Consultant)
'Date created: 12 July, 2010
Private Sub cmdImportBaseline_Click()
Dim sPath As Variant
Dim sRecord As String 'Input string from csv file
Dim aRecord As Variant 'Record set of input string
Dim lNextRow As Long 'To count filled rows
Dim Count As Integer
Dim ShowMsg As VbMsgBoxResult
Dim strMessage As String
Dim Validate As Boolean


Application.ScreenUpdating = False
Count = 4 ' initialising with first row containing data
sPath = Application.GetOpenFilename("CSV Files (*.csv), *csv") ' get csv file containing baseline data

On Error GoTo errHandler
'user pressed Cancel
If sPath = False Then
ShowMsg = MsgBox("No File selected!", vbInformation, "File not selected")
Exit Sub
Else
'get the next available row
lNextRow = Range("A" & Rows.Count).End(xlUp).Row + 1

'open the csv file for input
Open (sPath) For Input As #1

'read the source file one line at a time into the variable sRecord
Line Input #1, sRecord
aRecord = Split(sRecord, ",")


'output the source file
Range("A" & lNextRow).Value = Trim(aRecord(0))
Range("B" & lNextRow).Value = Trim(aRecord(1))
Range("C" & lNextRow).Value = Trim(aRecord(2))
Range("D" & lNextRow).Value = Trim(aRecord(3))
Range("E" & lNextRow).Value = Trim(aRecord(4))
Range("F" & lNextRow).Value = Trim(aRecord(5))
Range("G" & lNextRow).Value = Trim(aRecord(6))
Range("H" & lNextRow).Value = Trim(aRecord(7))
Range("I" & lNextRow).Value = Trim(aRecord(8))
Range("J" & lNextRow).Value = Trim(aRecord(9))
Range("K" & lNextRow).Value = Trim(aRecord(10))
Range("L" & lNextRow).Value = Trim(aRecord(11))

End If
Application.ScreenUpdating = True
MsgBox ("Transfer done in row " & lNextRow & " of Baseline Sheet")

errExit:
Close #1
Exit Sub
errHandler:
If Err.Number > 0 And Err.Number <> 9 Then MsgBox Err.Number & " " & Err.Description
If Err.Number = 9 Then
Range("A" & lNextRow).Value = ""
Range("B" & lNextRow).Value = ""
Range("C" & lNextRow).Value = ""
Range("D" & lNextRow).Value = ""
Range("E" & lNextRow).Value = ""
Range("F" & lNextRow).Value = ""
Range("G" & lNextRow).Value = ""
Range("H" & lNextRow).Value = ""
Range("I" & lNextRow).Value = ""
Range("J" & lNextRow).Value = ""
Range("K" & lNextRow).Value = ""
MsgBox (" Please select correct csv file to import data into Baseline sheet or check data in the csv file being imported")
End If
Resume errExit
End Sub
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
ManvinderKaur said:
Hi I am a command button for exporting data as csv file and Importting data from csv file. I have a problem while exportting and importing.
I have range E43 that have drop down values containing comma value for example "Fishing, Mining company" when I iport this data instead of going into one cell that goes two separate cells. Hoe to fix this?

export code
Private Sub cmdSendBaselineData_Click()
Dim sPath As Variant
Dim sOutput As String
Dim csvSendData As VbMsgBoxResult
On Error GoTo errHandler

'call for funtion Validate data for data validation
If ValidateDataBaseline = False Then
Exit Sub
End If

GetFileName:
'get Path to save file as csv
sPath = Application.GetSaveAsFilename(ActiveWorkbook.Path & "\" & "Baseline" & Format(Now, "yyyymmddhmmss") & ".csv", FileFilter:="Excel Files (*.csv), *.csv", Title:="Save As")
If sPath = False Then
csvSendData = MsgBox("No csv file created", vbInformation)
Exit Sub
End If
If Dir(sPath) <> "" Then
ans = MsgBox("File already exists, you can not overwrite existing file?", vbRetryCancel)
If ans = vbRetry Then
GoTo GetFileName
ElseIf ans = vbCancel Then
Exit Sub
End If
End If

'build the output string
sOutput = Sheets("1.0 Business Details").Range("E12").Value & "-" & Sheets("1.0 Business Details").Range("E14").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E43").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E45").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("A42").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("A43").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E13").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N12").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E15").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N15").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E20").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E21").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("E22").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("I22").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N20").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N21").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N22").Value & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("R22").Value & ","
sOutput = sOutput & Sheets("2.0 Baseline Summary").Range("E11").Value & ","
sOutput = sOutput & Sheets("2.0 Baseline Summary").Range("E12").Value & ","
sOutput = sOutput & Sheets("2.0 Baseline Summary").Range("I12").Value & ","
sOutput = sOutput & Format(Sheets("1.0 Business Details").Range("E32").Value, "mm-dd-yyyy") & ","
sOutput = sOutput & Format(Sheets("1.0 Business Details").Range("E33").Value, "mm-dd-yyyy") & ","
sOutput = sOutput & Sheets("1.0 Business Details").Range("N13").Value & ","
sOutput = sOutput & Sheets("2.1 Production Efficiency").Range("E8").Value & ","
'output to csv file
Open sPath For Append As #1
Print #1, sOutput
Close #1
csvSendData = MsgBox("A csv file has been created at" & _
vbCrLf & sPath & vbCrLf & "Please send this file as an email attachment to " & _
vbCrLf & "abc@.au", vbInformation + vbOKOnly, "Baseline Data")
errExit:
Close #1
Exit Sub
errHandler:
If Err.Number > 0 And Err.Number <> 75 Then MsgBox Err.Number & Err.Description
If Err.Number = 75 Then MsgBox ("You do not have specific permissions to create a file.")
Resume errExit
End Sub


Import code

'Import Baseline data from csv file
'Created by : Manvinder Kaur (Dialog Consultant)
'Date created: 12 July, 2010
Private Sub cmdImportBaseline_Click()
Dim sPath As Variant
Dim sRecord As String 'Input string from csv file
Dim aRecord As Variant 'Record set of input string
Dim lNextRow As Long 'To count filled rows
Dim Count As Integer
Dim ShowMsg As VbMsgBoxResult
Dim strMessage As String
Dim Validate As Boolean


Application.ScreenUpdating = False
Count = 4 ' initialising with first row containing data
sPath = Application.GetOpenFilename("CSV Files (*.csv), *csv") ' get csv file containing baseline data

On Error GoTo errHandler
'user pressed Cancel
If sPath = False Then
ShowMsg = MsgBox("No File selected!", vbInformation, "File not selected")
Exit Sub
Else
'get the next available row
lNextRow = Range("A" & Rows.Count).End(xlUp).Row + 1

'open the csv file for input
Open (sPath) For Input As #1

'read the source file one line at a time into the variable sRecord
Line Input #1, sRecord
aRecord = Split(sRecord, ",")


'output the source file
Range("A" & lNextRow).Value = Trim(aRecord(0))
Range("B" & lNextRow).Value = Trim(aRecord(1))
Range("C" & lNextRow).Value = Trim(aRecord(2))
Range("D" & lNextRow).Value = Trim(aRecord(3))
Range("E" & lNextRow).Value = Trim(aRecord(4))
Range("F" & lNextRow).Value = Trim(aRecord(5))
Range("G" & lNextRow).Value = Trim(aRecord(6))
Range("H" & lNextRow).Value = Trim(aRecord(7))
Range("I" & lNextRow).Value = Trim(aRecord(8))
Range("J" & lNextRow).Value = Trim(aRecord(9))
Range("K" & lNextRow).Value = Trim(aRecord(10))
Range("L" & lNextRow).Value = Trim(aRecord(11))

End If
Application.ScreenUpdating = True
MsgBox ("Transfer done in row " & lNextRow & " of Baseline Sheet")

errExit:
Close #1
Exit Sub
errHandler:
If Err.Number > 0 And Err.Number <> 9 Then MsgBox Err.Number & " " & Err.Description
If Err.Number = 9 Then
Range("A" & lNextRow).Value = ""
Range("B" & lNextRow).Value = ""
Range("C" & lNextRow).Value = ""
Range("D" & lNextRow).Value = ""
Range("E" & lNextRow).Value = ""
Range("F" & lNextRow).Value = ""
Range("G" & lNextRow).Value = ""
Range("H" & lNextRow).Value = ""
Range("I" & lNextRow).Value = ""
Range("J" & lNextRow).Value = ""
Range("K" & lNextRow).Value = ""
MsgBox (" Please select correct csv file to import data into Baseline sheet or check data in the csv file being imported")
End If
Resume errExit
End Sub
Click to expand...

Could you change your delimiters to something like "~" instead of the ","s?
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA with 400000+ Combinations. Can it run faster?
Replies
1
Views
502
StephenCrump
S
S
  • Question
CSV export to remove column
Replies
1
Views
195
StellarRumble59
S
C
  • Question
Excel VBA CSV export query
Replies
1
Views
161
cerberus1845
C
S
  • Solved
Import csv file and append to existing table?
Replies
5
Views
741
shellp
S
C
  • Solved
Help adding deletion of rows before sorting?
2
Replies
18
Views
1K
cspengel
C

Forum statistics

Threads
1,216,117
Messages
6,128,937
Members
449,480
Latest member
yesitisasport

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top