ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¥Hado¤è¦¡¡A±NcsvÀÉ©ñ¶i°}¦C¤¤¥X¿ù¡H(·í¤¤¦³­^¤å¦r¥À)

¥Hado¤è¦¡¡A±NcsvÀÉ©ñ¶i°}¦C¤¤¥X¿ù¡H(·í¤¤¦³­^¤å¦r¥À)

¤p§ÌªºcsvÀɦp¤U¡G
5        5922        11.85        0        2,000
7        5926        11.9        5,000        7,000
9        6162        11.8        1,000        0
11        616K        11.9        1,000        0
13        700I        11.75        2,000        0
15        700I        11.9        1,000        0

¨C¦Cªº²Ä¤G­Ó¬O­^¤å»P¼Æ¦r²Õ¦¨¡A¨ä³£¬O¼Æ¦r
¦pªG¥H¥­±`ªº¤èªk©ñ¦bsheet¤W¡A¨S¦³°ÝÃD
­Y±NÀɮשñ¦b°}¦C¤¤¡A«h²Ä¤G­Ó·|¥X²{ªÅ­È¡A¦³®É­Ô¤]·|Åã¥Ünull
¦³¤°»ò¤èªk¥i¥HÅý¥¦¡A¤£·|¥X²{ªÅ­È¦Ó¥B¥i¥H©ñ¶i°}¦C¤¤©O¡H

·|·Q­n¥Î³o­Ó¤èªk¬O·Q­n¥[³t³B²z¼Æ¤d±icsv¡A¦pªG©ñ¦bªí®æ¤W³t«×®t¤W¤@¬q

§Úı±oÃöÁä¦b³o¥y¸Ü¡G
strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=no;IMEX=1;FMT=Delimited(,)"";"..

½ÐÀ°¦£

ªþ¤WÀÉ®×



temp.zip (105.74 KB)

http://club.excelhome.net/forum.php?mod=viewthread&tid=267031&pid=7230582&page=1&extra=#pid7230582
¦bºô¤W§ä°Ú§ä¡A²×©ó§ä¨ì¤@­Ó¦P¼Ë°ÝÃDªº¸Ñµª¡A«ö·Ó³Ì«áªºµù¥Uªí³]¸m¡A°ÝÃDÁ`ºâ¬O¸Ñ¨M¤F¡C
ºô¤å¦p¤U¡G
³s±µ¦r²Å¦ê¡G
¡]1¡^ODBC¡Gconnstr="Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=d:\data\;Extensions=asc,csv,tab,txt;"
¡]2¡^OLEDB¡Gconnstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\;Extended Properties=""text;HDR=Yes;FMT=Delimited"""
ª`·N¡G
<1>¸ô®|¡A§Ydbq©Îdata source¡A¥u¨ì¥Ø¿ý¤@¯Å¡A¤£«ü©w¤å¥ó¦W¡]¦bsql»y¥y¤¤«ü©w¡^
<2>HDR=YES¡Aªí¥Ü¼Æ¾Úªº²Ä¤@¦æ§@¬°ªíªº¼ÐÃD¦æ¡A¤]´N¬O¦C¦W¡A¥i¥H¥Î³o¨Ç¦C¦W¨Ó¿z¿ï¼Æ¾Ú

ª`·N¡A³s±µ¦ê¸Ì¦³IMEX=1¡AÁÙ¤£°÷¡AÁÙ±o­×§ïµù¥Uªí¡A±NImportMixedTypes³]¸m¬°Text¤~¦æ¡C

Jet ©M ODBC ´£¨Ñµ{§Ç±Nªð¦^¦û¦h¼ÆªºÃþ«¬ªº¼Æ¾Ú¡A¦ý¹ï©ó¦û¤Ö¼Æªº¼Æ¾ÚÃþ«¬¡A«h·|ªð¦^ NULL¡]ªÅ¡^­È

§ä¤F¤@¤Ñ²×©ó¸Ñ¨M§Úªº°ÝÃD¡IÁÂÁÂexcel home½×¾Â
¤£¹L­n­×¥¿¤@¤U

µù¥Uªí¤À32»P64¦ì¤¸

ªþ¤W§Ú¦¨¥\­×§ïªºµe­±

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format


HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Text

TOP

¦^´_ 2# joey0415
´£¨Ñ§A°Ñ¦Ò¡G
  1. Sub CSV_Import()
  2.     Dim ws As Worksheet, strFile As String

  3.     Sheets("ÀɮצWºÙ").Select
  4.     Cells.Clear
  5.    
  6.     Set ws = ActiveSheet 'set to current worksheet name
  7.     '  strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
  8.     '  §A¤]¥i¥Hª½±µ«ü©wÀɮצWºÙ
  9.     strFile = ThisWorkbook.Path & "\9962.csv"

  10.     With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
  11.          .TextFileParseType = xlDelimited
  12.          .TextFileCommaDelimiter = True
  13.          .Refresh
  14.     End With
  15. End Sub
½Æ»s¥N½X

TOP

¦^´_ 1# joey0415
  1. Sub CSV_Import()
  2.     Dim ws As Worksheet, strFile As String, i As Long
  3.     Dim arr
  4.    
  5.     Sheets("ÀɮצWºÙ").Select
  6.     Cells.Clear
  7.    
  8.     Set ws = ActiveSheet 'set to current worksheet name
  9.     '  strFile = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Please select text file...")
  10.     '  §A¤]¥i¥Hª½±µ«ü©wÀɮצWºÙ
  11.     strFile = ThisWorkbook.Path & "\9962.csv"

  12.     With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1"))
  13.          .TextFileParseType = xlDelimited
  14.          .TextFileCommaDelimiter = True
  15.          .Refresh
  16.     End With
  17.    
  18.     arr = Range("A1:E" & Range("A" & Rows.Count).End(xlUp).Row)
  19.     For i = LBound(arr, 2) To UBound(arr, 1)
  20.         Debug.Print arr(i, 1) & ","; arr(i, 2) & ", " & arr(i, 3) & ", " & arr(i, 4) & ", " & arr(i, 5) & ";"
  21.     Next i
  22. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ joey0415 ©ó 2013-10-7 19:47 ½s¿è

¦^´_ 3# c_c_lai

§Ú¦Û¤v¸Õªºµ²ªG¬O¡Aquerytable«á¥¦«á©ñ¦bcells¤W¡A¤@®æ¤@­Ó©ñ¦n

¤§«e§Ú¤]¬O³o¼Ë°µ¡A¨S¦³¤å¦r°ÝÃD¡A¨S¦³null°ÝÃD

§Úªº·Qªk¬O¡A¦pªG¥i¥H¥´¶}csvÀÉ¡A¨Ã¥Bª½±µ©ñ¦b°}¦C¤¤·|¤ñ¸û§Ö

¦]¬°«e­±¨â­Ó¤èªk¡A³Ì«á³£­n©ñ¶i¸ê®Æ®w¤¤

¥i¯àÁÙ¦³¨ä¥¦¦n¤èªk¡A¤£¹L¥Ø«e§Ú¥u·|³o¨â©Û

¦pªG¦³§ó¦nªº¤èªk½Ð´£¨Ñ¤@¤U¡K

=========================
¥HQT¤èªk«á±qCELLS©ñ¶i¸ê®Æ®w
¥HADO«á©ñ¶i°}¦C«á¡A¦AÂà¶i¸ê®Æ®w

«eªÌ¡G1500­ÓCSV  ªá¶O1800~2000¬í
«áªÌ¡G1500­ÓCSV  ªá¶O1000~1100¬í

¦pªG¦³°ª¤â½Ð¦hÃö·Ó¡K
ÁÂÁÂ

TOP

­è­è¤S¸Õ¤F¤T¦¸¡A²Ä¤GºØ¤èªk¥­§¡¥u­n600¦h¬í¡I¶W§Ö¡I

TOP

        ÀR«ä¦Û¦b : ¬O«D·í±Ð¨|¡AÆg¬ü§@ĵ±§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD