［已解決］excel import csv line break 問題

bongbong3481

用excel import csv file ,發覺某個Col 的field 內有line break，導致import csv file 後走晒位， 請問有咩方法解決到?

解決方法：最近學緊python，發覺用pandas 幾行code 搞好［將csv load 入pandas ，再save as xlsx 格式］

fred2088

回復 1 #bongbong3481

多唔多? 小的話人手執咗先至import,

多的話, regen csv with another seperator

via HKEPC Reader for Android

bongbong3481

回覆 2# fred2088


10幾萬行，個csv file 由系統generate 出黎，不能gen 另一種seperator， 只能在import 時候想方法。

kwliu

https://superuser.com/questions/ ... csv-file-into-excel

bongbong3481

kwliu 發表於 2023-5-7 21:59

公司機沒咩工具，只有notepad 及excel,  試左提及的方法，都系吾得。 google 左d code,    只能試下用vbs 將line break remove.

jimking332

公司機沒咩工具，只有notepad 及excel,  試左提及的方法，都系吾得。 google 左d code,    只能試下用vbs ...
bongbong3481 發表於 2023-5-9 07:11

有冇dummy data放上黎試? 幾行就夠了

lamsir

回覆 1# bongbong3481

Answered by ChatGPT:

1. Sub RemoveLineBreaksFromCSV()
   
2. 
   
3.     Dim strFile As String
   
4.     Dim strText As String
   
5.    
   
6.     strFile = "C:\Users\username\Documents\example.csv" 'Change this to the file path of your CSV file
   
7.    
   
8.     'Read the contents of the CSV file
   
9.     Open strFile For Input As #1
   
10.     strText = Input$(LOF(1), 1)
    
11.     Close #1
    
12.    
    
13.     'Replace line breaks with a space character
    
14.     strText = Replace(strText, vbLf, " ")
    
15.    
    
16.     'Save the modified CSV file
    
17.     Open strFile For Output As #1
    
18.     Print #1, strText
    
19.     Close #1
    
20.    
    
21.     'Import the modified CSV file into Excel
    
22.     With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=Range("A1"))
    
23.         .TextFileParseType = xlDelimited
    
24.         .TextFileCommaDelimiter = True 'Change this to the delimiter used in your CSV file
    
25.         .Refresh
    
26.     End With
    
27.    
    
28. End Sub
    
29. 
    
30. This code first reads the contents of the CSV file into a string variable. It then replaces line breaks with a space character using the `Replace` function. The modified text is then saved back to the CSV file. Finally, the modified CSV file is imported into Excel using the `QueryTables.Add` method. You can customize this code to fit your specific needs, such as changing the file path and delimiter used in the CSV file.

複製代碼

bongbong3481

回覆 7# lamsir

試左吾得, excel 出得一行row

bongbong3481

回覆 6# jimking332


   d 資料是client 有關吾出得街,  吾知line break 是咩符號（只見到email 或電話分了2行），所以沒辦法人肉整一個sample 出黎

狂蜂一號

https://www.nirsoft.net/utils/csv_file_view.html