0

We have a .txt file with encoding UTF-16 LE (discussed here, as well). We need to load this file into an Azure SQL database. We are first trying to convert this file to a csv format by using Text Import Wizard of Data Excel 365 wizard. But if we use the ^|^,^|^ as a custom delimiter, the first and last columns still end up with ^|^ value.

Question: What may be possible solutions/work arounds for converting this type of file to csv?

Remarks: This is a huge file (1GB) with about 150 columns. Following is just a sample for explaining the scenario in this post.

Sample of the txt file:

^|^Col0^|^,^|^Col1^|^,^|^Col2^|^,^|^Col3^|^,^|^Col4^|^,^|^Col5^|^,^|^Col6^|^,^|^Col7^|^
^|^1234^|^,^|^4600869848^|^,^|^6000.00^|^,^|^2021-12-20 10:16:19.3600000^|^,^|^False^|^,^|^^|^,^|^^|^,^|^2^|^
^|^5431^|^,^|^3425143451^|^,^|^30000.00^|^,^|^2021-12-13 10:27:44.9030000^|^,^|^False^|^,^|^^|^,^|^^|^,^|^2^|^
.....................
............................

After using the delimiter ^|^,^|^ in Excel text import wizard enter image description here

nam
  • 21,967
  • 37
  • 158
  • 332

1 Answers1

0

Instead of mentioning the ^|^,^|^ as custom delimiter, you can mention comma as a delimiter, that will give you a result like below:

enter image description here

Then you can record a macro to replace the desired characters which is ^|^ after importing is done as mentioned in below link:

Create A Macro Code To Achieve Find And Replace Text In Excel

AnuragSharma-MSFT
  • 622
  • 4
  • 6