ceneiqe
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 ?
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 ?
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
Sid
here's a first stab at it; will probably need some work as your information is a bit sparse.
txtimp.zip
txtimp.zip
ASKER
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
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
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.
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
Could you please upload a sample?
Sid
ASKER
Thank you. One last question. Are all the files in the exact same format?
Sid
Sid
ASKER
Are all the files in the exact same format?
>> yes
>> yes
Thank you. Please give me a short while.
Sid
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
Sid
ASKER
compare all the columns except the date. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok, let me try first , thks
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
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
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) ?
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
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
ASKER
Ok, let me try and get back.
Thanks !
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.
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.
ASKER
I will review the codes by 10 June.
ASKER
hi ssaqibh, is it possible to copy the Full code for me ?
i think i got errors by inserting your new code. thks.
i think i got errors by inserting your new code. thks.
are the headers the same in each txt file?