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
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