We help IT Professionals succeed at work.

Create a macro to convert multiple .txt file to .xls file and place all data in .xls file

ceneiqe
ceneiqe asked
on
587 Views
Last Modified: 2012-05-11
I would like the macro to do the following :

convert multiple .txt files in a folder
should delimit the .txt files so that there are headers in each column
format for date (1st column) should be in DDMMYY format
place all data from .txt files into 1 .xls file
scan through the data in .xls file
ensure all data are unique (ie. no repeated data in each row)

Is the above feasible ?

Comment
Watch Question

Robert SchuttSoftware Engineer
CERTIFIED EXPERT

Commented:
could you provide some example contents?
are the headers the same in each txt file?

Author

Commented:
yes. headers the same as the txt  file.
Could you please provide 2 sample text files so that I can test it before uploading a sample

Sid
Robert SchuttSoftware Engineer
CERTIFIED EXPERT

Commented:
here's a first stab at it; will probably need some work as your information is a bit sparse.

 txtimp.zip

Author

Commented:
Can you please upload a complete text file as it contains just 4 lines

That way I can give you a macro will will work perfectly. If you want to do it on your own, then see this link :)

https://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26981715.html

Sid

Author

Commented:
the data are all numbers. numerical values under the headers.
for date is in YY-MM-DD format.
and i would like toconvert them to DD-MM-YY format.

I repeat :)

Could you please upload a sample?

Sid
Thank you. One last question. Are all the files in the exact same format?

Sid

Author

Commented:
Are all the files in the exact same format?

>> yes
Thank you. Please give me a short while.

Sid
ceneiqe:The code is ready. Just want to confirm. When you say duplicates, do you mean same KG-GROSS value or do you want to compare all the columns except the date?

Sid

Author

Commented:
compare all the columns except the date. Thanks.
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
ok, let me try first , thks

Author

Commented:
the dates are in wrong format.

it should change from yy-mm-dd to dd-mm-yy format

02-11-13  which should be 13-02-11 (in dd-mm-yy format)
03-11-13
02-11-06
01-11-16
01-11-22
01-11-30
02-11-03
02-11-12
12-10-27
03-11-20
03-11-12
04-11-10
04-11-10
04-11-10
04-11-10
02-11-20
02-11-04
03-11-06
01-11-23
01-11-15
01-11-01
05-11-02
04-11-16
04-11-16
04-11-16
04-11-16
04-11-23
04-11-23
04-11-23
04-11-23
02-11-02
12-10-26
01-11-09
02-11-26
04-11-09
04-11-09
04-11-09
04-11-09
01-11-03
02-11-27
04-11-17
04-11-17
04-11-17
04-11-17
03-11-05
04-11-02
04-11-02
04-11-02
01-11-29
02-11-05
03-11-27
03-11-27
01-11-17
04-11-24
04-11-24
04-11-24
04-11-24
02-11-01
03-11-26
01-11-24
04-11-03
04-11-03
04-11-03
04-11-30
05-11-01
01-11-08
03-11-19
04-11-25
04-11-25
04-11-25
04-11-25
03-11-28
03-11-28
03-11-28
01-11-02
12-10-28
02-11-19
01-11-21
02-11-07
04-11-22
04-11-22
04-11-22
04-11-22
12-10-29
01-11-31
02-11-09
01-11-10
01-11-04
12-10-31
02-11-08
02-11-14
04-11-11
04-11-11
04-11-11
04-11-11
05-11-03
03-11-14
02-11-15
04-11-18
04-11-18
04-11-18
04-11-18
02-11-16
03-11-07
02-11-10
01-11-19
01-11-25
04-11-26
04-11-26
04-11-26
04-11-19
04-11-19
04-11-19
04-11-19
01-11-28
03-11-21
04-11-05
04-11-05
04-11-05
04-11-05
02-11-28
01-11-18
03-11-15
04-11-21
04-11-21
04-11-21
04-11-21
02-11-18
12-10-30
03-11-09
02-11-11
04-11-14
04-11-14
04-11-14
04-11-14
04-11-13
04-11-13
04-11-13
04-11-13
01-11-05
04-11-08
04-11-08
04-11-08
04-11-08
01-11-27
01-11-07
04-11-12
04-11-12
04-11-12
04-11-12
01-11-26
04-11-07
04-11-07
04-11-07
04-11-07
03-11-16
03-11-22
03-11-18
04-11-06
04-11-06
04-11-06
04-11-06
01-11-12
03-11-02
03-11-23
02-11-21
01-11-20
03-11-03
03-11-08
01-11-13
04-11-15
04-11-15
04-11-15
04-11-15
03-11-10
04-11-27
04-11-27
04-11-29
03-11-17
03-11-24
01-11-11
02-11-25
02-11-25
03-11-04
03-11-11
02-11-22
01-11-14
04-11-04
04-11-04
04-11-04
04-11-04
03-11-30
03-11-30
03-11-30
02-11-23
04-11-01
04-11-01
04-11-01
03-11-31
03-11-31
03-11-31
03-11-01
03-11-29
03-11-29
03-11-29
01-11-06
02-11-24
02-11-17
04-11-28
04-11-20
04-11-20
04-11-20
04-11-20

Author

Commented:
the macro is ok except the date format has gone weird.
anyone knows how to convert the text to date format (dd/mm/yy) ?
Saqib HusainEngineer
CERTIFIED EXPERT

Commented:
Insert this code at line 67.

Remember this is only to assist Sid who appears to be away. NOT FOR POINTS

Saqib
For Each cel In Range("A:A")
if cel<>"" then
sl2 = InStr(InStr(cel.Text, "/") + 1, cel.Text, "/")
cel.Offset(0, 1).Value = CVDate(Format(Right(cel.Text, Len(cel.Text) - sl2), "00") & "/" & Left(cel, sl2 - 1))
endif
Next cel

Open in new window

Author

Commented:
Ok, let me try and get back.
Thanks !
CERTIFIED EXPERT

Commented:
I've requested that this question be closed as follows:

Accepted answer: 200 points for SiddharthRout's comment http:/Q_26980319.html#35489706

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Author

Commented:
I will review the codes by 10 June.

Author

Commented:
hi ssaqibh, is it possible to copy the Full code for me ?

i think i got errors by inserting your new code. thks.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.