Agar menghapus data sebelumnya , maka perlu diubah :Sub MacroCVS()
On Error Resume Next
ActiveSheet.QueryTables.Item("data_1").Delete
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\data.csv", _
Destination:=Range("$A$1"))
.Name = "data_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
'TEXT MYD GENERAL
.TextFileColumnDataTypes = Array(2, 3, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub
To: [email protected]
From: [email protected]
Date: Tue, 25 Oct 2011 09:02:41 +0000
Subject: RE: [belajar-excel] Mengubah csv menjadi txt
untuk pengaturan tanda pemisah desimal dan ribuan gunakan :
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
To: [email protected]
From: [email protected]
Date: Tue, 25 Oct 2011 08:49:47 +0000
Subject: RE: [belajar-excel] Mengubah csv menjadi txt
maksudnya mengubah nama ? atau import file ?
kalau import :
text yg ada ',' ( koma ) perlu diubah seperti ini "emapt,lima" pada format CVS
isi data.txt (tab delimiter):
Text Tanggal Numeric
satu 5/16/2011 12333
dua 12/5/2011 4567.4
tiga 12/12/2011 3444.56
"emapt,lima" 11/11/2011 1111
isi data.csv ( comma delimiter) :
Text,Tanggal,Numeric
satu,5/16/2011,12333
dua,12/5/2011,4567.4
tiga,12/12/2011,3444.56
"emapt,lima",11/11/2011,1111
hasil :
Text
Tanggal
Numeric
satu
5/16/2011
12333
dua
12/5/2011
4567.4
tiga
12/12/2011
3444.56
emapt,lima
11/11/2011
1111
"emapt,lima" diubah menjadi emapt,lima pada data.cvs :
Text
Tanggal
Numeric
satu
5/16/2011
12333
dua
12/5/2011
4567.4
tiga
12/12/2011
3444.56
emapt
lima
11/11/2011
1111
Sub MacroCVS()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\data.csv", _
Destination:=Range("$A$1"))
.Name = "data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
'TEXT MYD GENERAL
.TextFileColumnDataTypes = Array(2, 3, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
End Sub
Sub MacroTXT()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;E:\data.txt", _
Destination:=Range("$A$1"))
.Name = "data"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
'TEXT MYD GENERAL
.TextFileColumnDataTypes = Array(2, 3, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sheets("Sheet2").Select
End Sub
2. kalau rename file
kill "e:\data.txt" ' hapus file lama
Name "e:\data.cvs" As
"e:\data.txt"
To: [email protected]
From: [email protected]
Date: Tue, 25 Oct 2011 11:52:50 +0700
Subject: [belajar-excel] Mengubah csv menjadi txt
Hi teman2,
saya mau tanya bagaimana meng-Ubah nama suatu file dari sample.csv menjadi
sample.txt tanpa perlu membukanya di excel terlebih dahulu
Saya coba mengolah yang csv tetapi parsing datanya error, tetapi kalau txt
parsingnya lancar.
Terima kasih atas bantuannya
Salam,
Yulius
===========================================
mod's Note:
Barang kali sambil dicoba juga di-rename
di windows-explorer *.csv menjadi *.txt
lalu hasil parsingnya spt apa
Komentar lain sulit, berhubung tiadanya
contoh file yg BOLEH dilihat
===========================================
