Link to home
Start Free TrialLog in
Avatar of ceneiqe
ceneiqeFlag for Australia

asked on

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

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 ?

Avatar of Robert Schutt
Robert Schutt
Flag of Netherlands image

could you provide some example contents?
are the headers the same in each txt file?
Avatar of ceneiqe

ASKER

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
here's a first stab at it; will probably need some work as your information is a bit sparse.

 txtimp.zip
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/questions/26981715/excel-convert-format-issue.html

Sid
Avatar of ceneiqe

ASKER

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
Avatar of ceneiqe

ASKER

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
Avatar of ceneiqe

ASKER

compare all the columns except the date. Thanks.
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ceneiqe

ASKER

ok, let me try first , thks
Avatar of ceneiqe

ASKER

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
Avatar of ceneiqe

ASKER

the macro is ok except the date format has gone weird.
anyone knows how to convert the text to date format (dd/mm/yy) ?
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

Avatar of ceneiqe

ASKER

Ok, let me try and get back.
Thanks !
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.
Avatar of ceneiqe

ASKER

I will review the codes by 10 June.
Avatar of ceneiqe

ASKER

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

i think i got errors by inserting your new code. thks.