Text file to excel back to text file

DRExcel515

Board Regular
Joined
Oct 20, 2017
Messages
56
So I have a massive text file I receive each month that I need to make edits to and then load to a database. I would love to be able to convert this text file into excel and make my edits there (save hours of time) and then convert it back to a text file. I can open the text file fine with excel, but after making my edits and trying to save back to a text file I get quotes around all the numbers and fund codes (70E1 change to scientific notation like 7.00e+01). This will then cause the file not to load properly into my database and fail.

Original Format before opening in EXCEL:
Effective Date Fund Code Fund Name Security Identifier Security Holdings Share Quantity Base Market Value Coupon Maturity Date Base Currency Local Market Value Local Currency Local Cost Issue Country
6/30/2018 7000 T. Rowe Price Japan Fund 6127334 VT HOLDINGS CO LTD 1,039,200.000 5,462,390.06 0.000 USD 604,768,516.00 USD 573,356,633.00 JP
6/30/2018 7000 T. Rowe Price Japan Fund 6129277 NTT DOCOMO INC 825,900.000 21,045,890.02 0.000 USD 2,330,095,714.00 USD 1,974,528,403.00 JP
6/30/2018 7000 T. Rowe Price Japan Fund 6183552 CENTRAL JAPAN RAILWAY CO 21,400.000 4,430,425.97 0.000 USD 490,514,611.00 USD 351,937,094.00 JP
6/30/2018 7000 T. Rowe Price Japan Fund 6196408 CHUGAI PHARMACEUTICAL CO LTD 355,100.000 18,593,242.55 0.000 USD 2,058,550,849.00 USD 1,403,494,879.00 JP


What happens when I try to save it back:
06/30/2018 70DX T. Rowe Price Global Allocation Fund 45614 ANTOFAGASTA PLC "18,870.00" "245,162.59" 0 USD "185,764.60" USD "146,166.16" GB
06/30/2018 7.00E+01 T. Rowe Price Personal Strategy Balanced Portfolio 45614 ANTOFAGASTA PLC "6,698.00" "87,021.68" 0 USD "65,938.07" USD "41,668.32" GB
06/30/2018 70H1 T. Rowe Price International Value Equity Fund 45614 ANTOFAGASTA PLC "3,338,922.00" "43,379,903.70" 0 USD "32,869,820.63" USD "26,845,202.88" GB
06/30/2018 70J3 T. Rowe Price International Equity Index Fund 45614 ANTOFAGASTA PLC "12,588.00" "163,545.66" 0 USD "123,921.82" USD "55,972.54" GB

Any ideas/help would be much appreciated!
Cheers :)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
The biggest issue is that when saving it back to a text file I get all the "" around the numbers. IF I could somehow avoid that it would work!
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top