Convert txt. file to excel file <>excel to txt. File Layout 116 characters - price databas

Dear all,

Please be kind and help me with below request:

1. I need your help in order to identify an easy and efficient way to convert a txt. file to excel file – fyi: this file has over 600k lines and 78 MB. (please see attached file as example).

2. I need a way to convert from excel file into txt. but this must keep the same format as “file example” - File Layout 116 characters. After I have to change some information in excel and then to save it again in txt., in the end I must import txt. file in Microsoft Navision 2009 as a price database.

Can anyone give me a solution for this issue ?

Many thanks,
Costin

Hello Leith Ross,

Yes, txt. file has 5 fixed width columns, but in the end excel file will be as below (each character means something):

File Layout:
01-18 Part Number
19-58 Part Description
59-59 Flag price
60-60 Free
61-68 Price date (YYYYMMDD)
69-79 List price (2decimals position, decimal point not included)
80-92 Weight of the Material in kg (3decimals position, decimal point not included)
93-97 Pack Quantity
98-98 Free
99-101 1st Product line
102-102 Discount Code
103-107 PCC
108-112 MPC
113-113 Return Indicator
114-116 Free

Thank you,
Costin

Hello mehmetcik,

This works, but all the information must be divided as File Layout above.

Thank you,
Costin

costi_linho12

Can you just upload a source text file and the workbook with your desired result clearly showing what should be done?

Hello jindon,

Thanks for your reply, attached you can see txt. file & excel workbook. The excel file must be converted in txt. file, but is very important to have the same result as txt. file attached.

The excel file must respect below file layout.

For example I have a price 1012 and I don’t know how to convert in 69-79 List price (2decimals position, decimal point not included)…

File Layout:
01-18 Part Number
19-58 Part Description
59-59 Flag price
60-60 Free
61-68 Price date (YYYYMMDD)
69-79 List price (2decimals position, decimal point not included)
80-92 Weight of the Material in kg (3decimals position, decimal point not included)
93-97 Pack Quantity
98-98 Free
99-101 1st Product line
102-102 Discount Code
103-107 PCC
108-112 MPC
113-113 Return Indicator
114-116 Free


Thank you,
Costin

Is this how you wanted?

Please Login or Register  to view this content.

file example - final.xlsxfile example - result.xlsm


Hello,

Yes, in .xlsm works, but in field 69-79 List price (2decimals position, decimal point not included) – we have 11 character not 10 (in txt. we have 00000002295, in .xlsm we 0000000229), and in field 99-101 1st Product line we have 3 character not 2 – I modified in excel file from 2 character to 3 character (my mistake, sorry). Can you recheck.

Also, do you think this VBA formula will work with aprox. 700.000 rows ?

Thank you,
Costin

Then....

Please Login or Register  to view this content.

Yes, in .xlsm works perfectly, but this is missing 114-116 Free - 3 characters ....

Jindon, you solve first request, do you think is possible to solve second one ?

2. I need a way to convert from excel file into txt. but this must keep the same format as “file example.txt‎” - File Layout 116 characters.

Finally “file example - final - 2 excel” should look as text “file example.txt” - File Layout as well.

Thank you,
Costin

1)

Please Login or Register  to view this content.

2) try

Please Login or Register  to view this content.

Change
CreateObject("WScript.Shell").Specialfolders("desktop") to suite

1) y = Array(Array(1, 18), Array(19, 10), Array(59, 1), Array(60, 1), Array(61, 8), _
Array(69, 11), Array(80, 13), Array(93, 5), Array(98, 1), Array(99, 3), _
Array(102, 1), Array(103, 5), Array(108, 5), Array(113, 1), Array(115, 3))

In .xlsm works perfectly. Do you think this VBA formula will work with aprox. 700.000 rows (In this moment works perfectly only for 3 rows) ?

2) Is possible to change in VBA formula "." & "," from 80-92 Weight of the Material in kg (3decimals position, decimal point not included) - txt. file must be without "." & "," ?


Conversion from excel to txt. is not correct.

Should be like below:

1113 040012 CAPSCREW # 2015010500000002295000000000000000001 92AAV648KA0100Y

What I have is:


1113 040012 CAPSCREW # 2015010520252.51 92AAV648KA0100Y


Thank you,
Costin

Which data are you talking?
I got this as a result from the code.

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I mean which Excel file are you talking about?
There was no decimal point at all.

Yes, you're right, my mistake, sorry about that.I tested your VBA formula in attached file,also attached the result (You can see that is not correct).

Is not easy for me, because is the first time when I work with VBA…

Thank you,
Costin

costi_linho12,

You need to edit your post #15 and add code tags
[code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code]

Done.

Thank you,
Costin

Thanks and password removed.

Thank you, but the code is not ok – field “01-18 Part Number” should be unchanged (this is the part no. and will be imported in the system with “.”, “-“, “ “).

All fields from excel should be exactly as below File Layout:

01-18 Part Number
19-58 Part Description
59-59 Flag price
60-60 Free
61-68 Price date (YYYYMMDD)
69-79 List price (2decimals position, decimal point not included)
80-92 Weight of the Material in kg (3decimals position, decimal point not included)
93-97 Pack Quantity
98-98 Free
99-101 1st Product line
102-102 Discount Code
103-107 PCC
108-112 MPC
113-113 Return Indicator
114-116 Free

Total characters 116 (including blank).

Example:

1113 040012 CAPSCREW # 2015010500000002295000000000000000001 92AAV648KA0100Y

What we have :


1113 040012 CAPSCREW # 20150105001251 92AAV648KA0100Y

Is to short…

Thanks again for your help and support !
Costin

I'm confusing...

Can you just upload your source workbook and the result text file that you want?

Try

Please Login or Register  to view this content.

The result below:

1113 040012 CAPSCREW # 2015010522950000000000000000000010000 92AAV648KA0100Y

What I need:

1113 040012 CAPSCREW # 2015010500000002295000000000000000001 92AAV648KA0100Y

The issue is here # 2015010522950000000000000000000010000.

Can you please, recheck the code ?

Thank you,
Costin

I see,

Please Login or Register  to view this content.

If bulky data

Please Login or Register  to view this content.

I've added a few part no. and I have error message – “Invalid procedure call or argument” and txt. test is blank….(I changed in excel file Excel Format Cells from General in TEXT for part no. with landmarks to 0).

Can you please, check the code ?

Thank you,
Costin

Always upload the file when you got error otherwise nobody will even guess what is going on,

Now you got me,I do not know what you mean ... with upload.

Ok, i have attached result.

Can you remove the space between rows and the part no. is without “.”. Can you recheck the code ?

Thank you,
Costin

Copy my last code again and try.

Now is ok with the blank, but is not ok with “.” from p/n, in txt. file is without “.” and must be with (where is the case). Can you change in code ?

Thank you,
Costin

I don't understand what you are talking about!

Just upload correct files that clearly shows what/how should be done.
You can't tell me in one row of data.... poor persentation....

let me give you a full excel with p/n, with all format data.

So what's the problem?

The code produces exactly the same as the text file you have attached.

We have the issue in field:
01-18 Part Number
101.018
101.080

In excel file we have p/n with "." and in txt. file is without ".".

Thank you,
Costin

Sorry I don't think I understand, wait for someone else to come in to help you.

jindon,

Please, you have one more step to finish this request .

You must change in your code – information from 01-18 Part Number, VBA code must take into account the diacritics like “.”, “/”, “-“ , “ “.

Thank you,
Costin

Then YOU MUST present the data including such characters and the text file that shows EXACTLT how you want.

I don't want to do a guess job anymore.

Please find attached.

Look at information from excel file, here the p/n is with “.”:

01-18 Part Number
101.018
101.080
CAM4.00/60-15
ANV10.0/75-15.3
ANV12.5/80-18 IND
CAM 16.9/18.4-34
01/117901
02/100073A
02/201931
331023090
033156F
337005140
0420.7101.00
101045-1
102.40.367.0
103.40.048.0
107.54.013.0
101-11175
15-167-01
180RH-93-R
180RH-93-L

Look at information from txt. file, here the p/n is without “.”:

101018 TEST 1 # 2015010500000000001000000000000100010 000
101080 TEST 2 # 2015010500000001000000000000010000010 000
CAM400/60-15 TEST 3 # 2015010500000000010000000000000100001 000
ANV100/75-153 TEST 4 # 2015010500000000011000000000000200002 000
ANV125/80-18 IND TEST 5 # 2015010500000000012000000000000300003 000
CAM 169/184-34 TEST 6 # 2015010500000000013000000000000400004 000
01/117901 TEST 7 # 2015010500000000014000000000000500005 000
02/100073A TEST 8 # 2015010500000000015000000000000600006 000
02/201931 TEST 9 # 2015010500000000016000000000000700007 000
331023090 TEST 10 # 2015010500000000017000000000000800008 000
033156F TEST 11 # 2015010500000000018000000000000900009 000
337005140 TEST 12 # 2015010500000000019000000000001000010 000
0420710100 TEST 13 # 2015010500000000020000000000001100011 000
101045-1 TEST 14 # 2015010500000000021000000000001200012 000
102403670 TEST 15 # 2015010500000000022000000000001300013 000
103400480 TEST 16 # 2015010500000000023000000000001400014 000
107540130 TEST 17 # 2015010500000000024000000000001500015 000
101-11175 TEST 18 # 2015010500000000025000000000001600016 000
15-167-01 TEST 19 # 2015010500000000026000000000001700017 000
180RH-93-R TEST 20 # 2015010500000000027000000000001800018 000
180RH-93-L TEST 21 # 2015010500000000028000000000001900019 000

jindon, please tell me if now is clear ?

Thank you,
Costin

Hello jindon,

Still in process of testing your code and I found a small mismatch in txt. file, see below:

944026130 SUPPORT # 2015033100000000782000000000205000001 17AGS454BC0200Y - result from jindon code

944026130 SUPPORT # 2015033100000007820000000000205000001 17AGS454BC0200Y - correct result


My request to you is to change in your code (I also, attached a picture for better understanding).

Thank you,
Costin

And is not possible to change in VBA code ?

Do it for yourself.
You could change the numbers and find out what you want.
I really do not understand what you really want.

I tried, but I don’t know where to change in VBA code.

944026130 SUPPORT # 2015033100000000 - here is one”0” in plus towards correct result 782000000000 - here is one”0” in minus towards correct result 205000001 17AGS454BC0200Y - result from jindon code

Like below must be the result:
944026130 SUPPORT # 2015033100000007820000000000205000001 17AGS454BC0200Y - correct result

jindon, please tell me if now is clear ?

Thank you,
Costin

Dear jindon,

Please be kind and tell me if you can help me with a solution for post #44 and post #49 ?

I insist because I want to finish this issue with your help (we're almost there ).

Again, thanks for your help and support !
Costin

Hello jindon,

Still testing your code and I found that my issue from post #44 is in this part of code:
a = .Substitute(.Substitute([a1].CurrentRegion.Value, ",", ""), ".", "") - now if I remove "." the information from 01-18 Part Number will appear in txt. file with “.” as below:

101.018 TEST 1 # 2015010500000000.0100000000000.100010

Still the issue remain here 2015010500000000.0100000000000.100010

Now can you change in VBA code as following ?

- 01-18 Part Number – must keep in txt. file “.”

- 69-79 List price (2decimals position, decimal point not included) & 80-92 Weight of the Material in kg (3decimals position, decimal point not included) – “.” must remove in txt. file

Thank you,
Costin

Hello,

Can you tell me why below code dose not import from .xlsm to .txt more than 65,536 rows, if I modify to 65,537 rows I have “ Type mismatch” ?

Thank you,
Costin

Please Login or Register  to view this content.

Dear all,

Can someone help me with a solution for post 52 & 53 ?

It is very important for me to solve this issue .

Thank you,
Costin

Issue solved with help of a colleague.

jindon, thanks for your help ans support !

Please Login or Register  to view this content.

Dear all,

First I want to THANK YOU for your help and support ! I learned many things that made my job easier.

It seems that this old issue is not yet solved in my case …

Now I want to do a conversion from xlsm. into txt. with below VBA code, but I don’t know why I have error code “Type mismatch” when I want to convert more than 70.000 rows (I can do it till 60.000 rows). More information in attached files “file_ example_excel to txt_rev.03.10.2015.xlsm” & file_example_excel to txt_rev.03.10.2015.txt”.

Please Login or Register  to view this content.

In the end I want to covert approximately 700.000 rows. Can anyone give me a solution for this issue ? Any advice would be appreciated.

Many thanks,
Costin

Hello daffodil11,

Thanks for your fast reply and help. In this case, I will split source file in more files, max. 65,536 rows and then past in txt. file…

Regards,
Costin

It would be great automatically adjust .

Thanks,
Costin

Yes, It works perfectly for 50.000 rows, no more.