Question about import.

[cyrilz]

Hello everybody here at PHD.


I'm a bit stuck with Date format while importing CSV files, and / or copying the data.

Here's what I try to do.


Consider 2 bug defect systems capable to export to CSV (tab separated). The structure of the data is like this :


System 1 :

Bug ID1 / Author / Date / Priority 1 / Title (short) / Description (can be very long and contains special character) / Project Code


System 2 :

[code]Bug ID2 / Title (with special formatting sometimes)/ Solving status


I want to build a sheet containing the following entries :


[code]Bug ID1 / Bug ID2 / Date / Priority / Project Code / Solving Status / Title / Description

My firts idea is to import into separate sheets each CSV file. This seems to work with the following VBA code :

[pre]<br />
Set tempWkb = Workbooks.Open(ActiveWorkbook.Path & "workmyFile.xls&#34<br />
Range("A1", Selection.End(xlToRight)).Select<br />
Range(Selection, Selection.End(xlDown)).Select<br />
Selection.Copy Destination:=mySheet.Range("A1&#34<br />
tempWkb.Close SaveChanges:=False<br />[/code][/pre]
I've tried to use QueryTables.Add but the "description" field is totally imported, causing glitches in columns (special characters are badly handled).


Now I'm copying each line of each sheet trying to aggregate the data when they match (special rule in the title of system2).


The date column is now strange (French formatting in the first 2 lines / US after):


16/07/2009 15:11:00

15/07/2009 15:10:33

07/10/2009 16:30

07/09/2009 18:59


When the day if less than 12 I've the second ones, the first format otherwise. The VBA Code (copying the date) is the following :


Sys1Sheet.Cells(Sys1_CurLine, 1).Range(Sys1_Range_Relative).Copy Destination:=Out.Cells(Out_Cur, Sys1_Start_Index).Range("A1&#34[/code]


My questions are :

1. Is there anyway to tell the format while copying without affecting each cells (perf issue) ?


2. Can I use/tell QueryTables.Add to import only the first xxx characters of a long string ?


3. Can I affect an array formula form one cell in VBA ? (to avoid the copy and thus retrieving the correct format)


Thanks in advance.


Cyril Z.


[EDIT] : If someone has a better idea... This forum seems a great idea anywya.

 

[keymaster]

Cyril...


I cant give exact solution.. but some thoughts...


regarding date formats: you can use VBA to pre-process the data and clean it up. You can access all the spreadsheet functions from with in VBA. So try using the DATE() formula to construct the date by parsing the input values. I am sure there are lot of possibilities for the input values.. but if you inspect the CSVs you will know what formats you should cover...


coming to special characters in description field: I think you can handle that by adjusting the values in the CSV (I am not sure, but I think if you precede , with a single quote, it will not be considered as comma by the import mechanism). But better thing would be to process the files as text files and read them from VBA.. this gives you more control on how you want to paste the data.


I know it is a lot of work.. let me know if you come across any interesting solutions for this..

 

[cyrilz]

Thanks Chandoo for your answer.


Unfortunately I've no control over the file generated. I must process it "as-is". The text written in the file is french-formatted correctly. Excel want to change the format of its own will. (weird isn't-it ?)


I've already used the QueryTables.add function, with some success with the date format (import as text) but the problem is that the Description field also contains tabs and quotes without englobing quotes. So columns are not respected.


When I open it from the directory, columns and formatting are OK. When I paste them the date format change.


Parsing the file in VBA can be too time-consuming (4800 lines to process) but I'll give it a try since I've no other solution.

 

[cyrilz]

Hello all,


Fortunately I've found something that helped me a lot.


Look at : Workbooks.open( filename := xxxx, Local:=True )


The same applies to SaveAs.


Then I use Microsoft Query to format my string properly, and now I've got :


2009-08-04

2009-08-03

2009-08-03

2009-08-03

2009-07-31

2009-07-31

2009-07-31


Which is what I expect.


Hope this help!