Forum Discussion
Solved
csv data separated by comma and space
Hello, I am trying to import csv text from the clipboard that is separated: fields by comma, and rows by space. Text import wizard does not appear to able to do this.
There is no dedicated web part for Draw.io (Sorry I don't know what this is) in SharePoint Online.
However, there is a generic File viewer web part in SharePoint Online. You can try using it if that works for you. Check below reference for more information on this web part:
Please click Mark as Best Response if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it b.
3 Replies
You are correct, Excel import wizard does not split a single line into multiple rows. So I see 2 possible options:
1) Use a text editor like Notepad++ to edit the CSV file and do the line breaks at the spaces. Save. Then reopen the CSV file in Excel.
2) if the file is a short file, you could import all the data into 1 cell. Then use Data / Text to Columns to split the data at the space character. That will create multiple columns where the data was split at the space. Then Copy all the data, Paste Special / Transpose will change the column data to Row data (but all data will still be in only 1 column. Finally select the column data - Data / Text to Columns again but this time delimited by comma.Hi jbell61
Maybe not an issue anymore but since you can use space as column delimiter, you can get the csv records cell by cell on one single row, row 1.
TRANSPOSE and you in a way have used space as row delimiter.
Example Input Data
Name,No,Color,Version Madelene,7,Yellow,PDA Erika,10,Yellow,PDA Christina,12,Yellow,PDA Daniella,7,Blue,PDA Elizabeth,7,Blue,PDA Elizabeth,6,Blue,365 Johanna,44,Blue,365
Opened and found at row 1 using the wizard with space as column delimiter.
A5 transposes the columns to rows
=TRANSPOSE(A1:H1)It can, using OFFSET, be extended to automatically adjust to the imported amount of data
=TRANSPOSE(OFFSET(A1;0;0;1;COUNTA(1:1)))Finally, use FILTERXML to split the csv data into columns by first making it look like XML code and then parse it into separate cells.
=(FILTERXML("<c><r>" & SUBSTITUTE($A5#;",";"</r><r>") & "</r></c>";"//r[1]"))
