Quantcast

[belajar-excel] Mengubah csv menjadi txt

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

[belajar-excel] Mengubah csv menjadi txt

yulius -
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
===========================================

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: [belajar-excel] Mengubah csv menjadi txt

jkssxls Sudarsono

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: [hidden email]
From: [hidden email]
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

===========================================





   
     

   
   






       
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: [belajar-excel] Mengubah csv menjadi txt

yulius -
it works [?]

Thanks

2011/10/25 jkssxls Sudarsono <[hidden email]>

>  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: [hidden email]
> From: [hidden email]
> 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
> ===========================================


------------------------------------

---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
[hidden email]

posting ke milis, kirimkan ke:
[hidden email]

berkunjung ke web milis
http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran
http://www.mail-archive.com/belajar-excel@.../
atau (sejak 25-Apr-2011) bisa juga di :
http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: [hidden email]

keluar dari membership milis (UnSubscribe):
kirim mail kosong ke  [hidden email]
---------------------------------------------------------------------Yahoo! Groups Links

<*> To visit your group on the web, go to:
    http://groups.yahoo.com/group/belajar-excel/

<*> Your email settings:
    Individual Email | Traditional

<*> To change settings online go to:
    http://groups.yahoo.com/group/belajar-excel/join
    (Yahoo! ID required)

<*> To change settings via email:
    [hidden email]
    [hidden email]

<*> To unsubscribe from this group, send an email to:
    [hidden email]

<*> Your use of Yahoo! Groups is subject to:
    http://docs.yahoo.com/info/terms/

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: [belajar-excel] Mengubah csv menjadi txt

jkssxls Sudarsono
In reply to this post by jkssxls Sudarsono

untuk pengaturan tanda pemisah desimal dan ribuan gunakan :         .TextFileDecimalSeparator = "."
        .TextFileThousandsSeparator = ","

 To: [hidden email]
From: [hidden email]
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: [hidden email]
From: [hidden email]
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

===========================================






   
     

   
   






       


   
     

   
   






       
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: [belajar-excel] Mengubah csv menjadi txt

jkssxls Sudarsono

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: [hidden email]
From: [hidden email]
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: [hidden email]
From: [hidden email]
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: [hidden email]
From: [hidden email]
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

===========================================






   
     

   
   






       



   
     

   
   






       


   
     

   
   






       
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: [belajar-excel] Mengubah csv menjadi txt

jkssxls Sudarsono

Agar menghapus data sebelumnya , maka perlu diubah :
Sub MacroCVS()
    On Error Resume Next    Application.Goto Reference:="data_1"
    Selection.ClearContents
    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: [hidden email]
From: [hidden email]
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: [hidden email]
From: [hidden email]
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: [hidden email]
From: [hidden email]
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

===========================================






   
     

   
   






       



   

     

   
   






       



   
     

   
   






       


   
     

   
   






       
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

RE: [belajar-excel] Mengubah csv menjadi txt

jkssxls Sudarsono

Jika saya menggunakan tabel ms access dan mengexport ( gunakan menu external data ) ke format text ( comma delimiter ) dan hasil export sbb ( nama field disertakan ) :"fText","fDate","fNumber"
"satu",5/16/2011 0:00:00,12333.00
"dua",12/5/2011 0:00:00,4567.40
"tiga",12/12/2011 0:00:00,3444.56
"emapt,lima",11/11/2011 0:00:00,1111.00 dan saya gunakan/jalankan  MacroCVS , meghasilkan sesuai apa yg saya harapkan....
To: [hidden email]
From: [hidden email]
Date: Tue, 25 Oct 2011 09:29:46 +0000
Subject: RE: [belajar-excel] Mengubah csv menjadi txt


















 



 


   
     
     
     


Agar menghapus data sebelumnya , maka perlu diubah :
Sub MacroCVS()
    On Error Resume Next
    Application.Goto Reference:="data_1"
    Selection.ClearContents
    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: [hidden email]
From: [hidden email]
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: [hidden email]
From: [hidden email]
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: [hidden email]
From: [hidden email]
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

===========================================






   
     

   
   






       



   


     

   
   






       



   
     

   
   






       



   
     

   
   






       


   
     

   
   






       
Loading...