Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.misc,microsoft.public.excel.programming
|
|||
|
|||
Importing CSV - Avoiding conversion to 'dates', but totally different results when controlling manually
Hi All,
I have to import CSV files into excel. A regular issue with this is that often the CSV files will contain data (in this case account codes) that excel interprets as dates (such as '3-8000' being converted to 1 Mar 8000). Nothing unusual so far, in that we handle that issue by importing manually (External Data - Text import), and specifying that the field in question is imported as TEXT not GENERAL. However, I have a file that, when I double click on it, it opens up perfectly (except with dates where it should be text as outlined above), but if I manually import that file, it then chokes on some control characters that are in the CSV file (specifically an ASCII Code 13 which is a carriage return). I haven't got much of a clue on what the best way to go from here would be, so looking for any suggestions. One possible idea that is probably a VBA solution (happy to go down that track if easiest) is to somehow do a search and replace on the source CSV file and convert the CRs (ASCII 13) to, say, spaces (ASCII 32). I think I'd need to do that to the external CSV file though before it got imported, hence the VBA requirement? Thanks in advance for any ideas. -- Alan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Importing CSV - Avoiding conversion to 'dates', but totally differ
Two suggestions;
1. Record a macro while importing the file and later edit that macro You will see an entry like following - FieldInfo:= Array( Array(0,1), Array(14, 1), Array(39, 9), Array(48, 1), Array(54, 1), Array(60, 1), Array(72, 1), Array(85, 1), Array(93, 1), Array(100, 1) ) Try to identify the column which is being treated as date and change 1 to 2 in that array entry... this should solve text getting imported as date problem. 2. Do you really get the file opened perfectly? I would guess that ASCII 13 would take the data to next row... Yes, you can replace it with a space character... ASCII 10 is treated as End of Record while importing a text file... -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Alan" wrote: Hi All, I have to import CSV files into excel. A regular issue with this is that often the CSV files will contain data (in this case account codes) that excel interprets as dates (such as '3-8000' being converted to 1 Mar 8000). Nothing unusual so far, in that we handle that issue by importing manually (External Data - Text import), and specifying that the field in question is imported as TEXT not GENERAL. However, I have a file that, when I double click on it, it opens up perfectly (except with dates where it should be text as outlined above), but if I manually import that file, it then chokes on some control characters that are in the CSV file (specifically an ASCII Code 13 which is a carriage return). I haven't got much of a clue on what the best way to go from here would be, so looking for any suggestions. One possible idea that is probably a VBA solution (happy to go down that track if easiest) is to somehow do a search and replace on the source CSV file and convert the CRs (ASCII 13) to, say, spaces (ASCII 32). I think I'd need to do that to the external CSV file though before it got imported, hence the VBA requirement? Thanks in advance for any ideas. -- Alan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Importing CSV - Avoiding conversion to 'dates', but totally differ
I you trying to convert the dates back to text? You can format the date
field to a custom number format like 0-0000. Then if you want to convert these to text you can select them and choose Data, Text to Columns, Next, Next, and select the Text option and click Finish. It this helps, please click the Yes button. -- Thanks, Shane Devenshire "Alan" wrote: Hi All, I have to import CSV files into excel. A regular issue with this is that often the CSV files will contain data (in this case account codes) that excel interprets as dates (such as '3-8000' being converted to 1 Mar 8000). Nothing unusual so far, in that we handle that issue by importing manually (External Data - Text import), and specifying that the field in question is imported as TEXT not GENERAL. However, I have a file that, when I double click on it, it opens up perfectly (except with dates where it should be text as outlined above), but if I manually import that file, it then chokes on some control characters that are in the CSV file (specifically an ASCII Code 13 which is a carriage return). I haven't got much of a clue on what the best way to go from here would be, so looking for any suggestions. One possible idea that is probably a VBA solution (happy to go down that track if easiest) is to somehow do a search and replace on the source CSV file and convert the CRs (ASCII 13) to, say, spaces (ASCII 32). I think I'd need to do that to the external CSV file though before it got imported, hence the VBA requirement? Thanks in advance for any ideas. -- Alan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Importing CSV - Avoiding conversion to 'dates', but totally differ
Hi again,
I think maybe my last suggestion won't do what you want - instead try this Assuming the first entry is in A1 enter this formula in a blank cell =MONTH(a1)&"-"&YEAR(a1). Choose Copy, Paste Special Values. -- Thanks, Shane Devenshire "Alan" wrote: Hi All, I have to import CSV files into excel. A regular issue with this is that often the CSV files will contain data (in this case account codes) that excel interprets as dates (such as '3-8000' being converted to 1 Mar 8000). Nothing unusual so far, in that we handle that issue by importing manually (External Data - Text import), and specifying that the field in question is imported as TEXT not GENERAL. However, I have a file that, when I double click on it, it opens up perfectly (except with dates where it should be text as outlined above), but if I manually import that file, it then chokes on some control characters that are in the CSV file (specifically an ASCII Code 13 which is a carriage return). I haven't got much of a clue on what the best way to go from here would be, so looking for any suggestions. One possible idea that is probably a VBA solution (happy to go down that track if easiest) is to somehow do a search and replace on the source CSV file and convert the CRs (ASCII 13) to, say, spaces (ASCII 32). I think I'd need to do that to the external CSV file though before it got imported, hence the VBA requirement? Thanks in advance for any ideas. -- Alan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Importing CSV - Avoiding conversion to 'dates', but totally differ
"Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Two suggestions; 1. Record a macro while importing the file and later edit that macro You will see an entry like following - FieldInfo:= Array( Array(0,1), Array(14, 1), Array(39, 9), Array(48, 1), Array(54, 1), Array(60, 1), Array(72, 1), Array(85, 1), Array(93, 1), Array(100, 1) ) Try to identify the column which is being treated as date and change 1 to 2 in that array entry... this should solve text getting imported as date problem. Hi Sheelo, I recorded the macro and this is what I got: +-+-+-+-+-+-+-+-+-+-+-+-+ Sub Macro1() With ActiveSheet.QueryTables.Add(Connection:="TEXT;file .txt", Destination:=Range("$A$1")) .Name = "importfile" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub +-+-+-+-+-+-+-+-+-+-+-+-+ The Array(1) bit is different to what you posted (you have two parameters)? Does that make any sense? 2. Do you really get the file opened perfectly? I would guess that ASCII 13 would take the data to next row... Yes, you can replace it with a space character... ASCII 10 is treated as End of Record while importing a text file... The ASCII 13 imports as a small 'square' character (when I described that it opened perfectly). I guess I was overstating it a bit, but the 'square' rather than a line break (CR) within a cell is not an issue for me, so it is relatively perfect ;-) If I was going to use VBA to edit the file prior to importing, how would I do that though? Thanks for your assistance, -- Alan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Importing CSV - Avoiding conversion to 'dates', but totally differ
"ShaneDevenshire" wrote in message ... I you trying to convert the dates back to text? You can format the date field to a custom number format like 0-0000. Then if you want to convert these to text you can select them and choose Data, Text to Columns, Next, Next, and select the Text option and click Finish. Hi Shane, The problem is that excel has effectively corrupted the underlying value of the data, rather then just formatting it wrongly. Using the example I did in my OP, the original text was "3-8000" (text string, six chars long). Excel imports that as a date (1 Mar 8000) which has an underlying value of 2228041 (numeric value). There is no simple formatting that will get from that numeric value back to the text string (as far as I know!) It this helps, please click the Yes button. Not sure what this means. The ms public groups are text only NNTP groups, so there is no concept of clicking a button? Thanks, -- Alan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Importing CSV - Avoiding conversion to 'dates', but totally differ
"ShaneDevenshire" wrote in message ... Hi again, I think maybe my last suggestion won't do what you want - instead try this Assuming the first entry is in A1 enter this formula in a blank cell =MONTH(a1)&"-"&YEAR(a1). Choose Copy, Paste Special Values. Hi Shane, I should have read down before replying to your first post! Yes, I could use formulae (or code) after the import to correct the issues, but I would really like to find a way to get excel to import the data correctly if I can. However, if that isn't possible, I may have to use a formula such as that, so thank you for posting it. The problem then would be that some of the cells are 'corrupted and some are not, but I can work through the scenarios if I can't resolve the underlying problem. Thanks again! -- Alan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb |
#8
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Importing CSV - Avoiding conversion to 'dates', but totally differ
"Alan" wrote in message
... "ShaneDevenshire" wrote in message ... I you trying to convert the dates back to text? You can format the date field to a custom number format like 0-0000. Then if you want to convert these to text you can select them and choose Data, Text to Columns, Next, Next, and select the Text option and click Finish. Hi Shane, The problem is that excel has effectively corrupted the underlying value of the data, rather then just formatting it wrongly. Using the example I did in my OP, the original text was "3-8000" (text string, six chars long). Excel imports that as a date (1 Mar 8000) which has an underlying value of 2228041 (numeric value). There is no simple formatting that will get from that numeric value back to the text string (as far as I know!) =TEXT(A1,"m-yyyy") -- David Biddulph It this helps, please click the Yes button. Not sure what this means. The ms public groups are text only NNTP groups, so there is no concept of clicking a button? Thanks, -- Alan. |
#9
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Importing CSV - Avoiding conversion to 'dates', but totally differ
"David Biddulph" <groups [at] biddulph.org.uk wrote in message ... "Alan" wrote in message ... "ShaneDevenshire" wrote in message ... I you trying to convert the dates back to text? You can format the date field to a custom number format like 0-0000. Then if you want to convert these to text you can select them and choose Data, Text to Columns, Next, Next, and select the Text option and click Finish. Hi Shane, The problem is that excel has effectively corrupted the underlying value of the data, rather then just formatting it wrongly. Using the example I did in my OP, the original text was "3-8000" (text string, six chars long). Excel imports that as a date (1 Mar 8000) which has an underlying value of 2228041 (numeric value). There is no simple formatting that will get from that numeric value back to the text string (as far as I know!) =TEXT(A1,"m-yyyy") Cool! If I can't find a way to sort out the source of the problem, then that'll definitely fix it at destination. Thanks, -- Alan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb |
#10
Posted to microsoft.public.excel.misc,microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
Importing CSV - Avoiding conversion to 'dates', but totally di
Apparently you did not select 'TEXT' as the field type for the column which
is getting imported as DATE... I assume you have that option in import through Query. I tested with importing a text file and got the following recorded macro... '------------------- Workbooks.OpenText Filename:= _ "C:\Test.txt", Origin:=437, StartRow _ :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _ ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=True, _ Space:=False, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array( _ 3, 1), Array(4, 1), Array(5, 1)), TrailingMinusNumbers:=True End Sub '------------------- -- Always provide your feedback so that others know whether the solution worked or problem still persists ... "Alan" wrote: "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" wrote in message ... Two suggestions; 1. Record a macro while importing the file and later edit that macro You will see an entry like following - FieldInfo:= Array( Array(0,1), Array(14, 1), Array(39, 9), Array(48, 1), Array(54, 1), Array(60, 1), Array(72, 1), Array(85, 1), Array(93, 1), Array(100, 1) ) Try to identify the column which is being treated as date and change 1 to 2 in that array entry... this should solve text getting imported as date problem. Hi Sheelo, I recorded the macro and this is what I got: +-+-+-+-+-+-+-+-+-+-+-+-+ Sub Macro1() With ActiveSheet.QueryTables.Add(Connection:="TEXT;file .txt", Destination:=Range("$A$1")) .Name = "importfile" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .TextFilePromptOnRefresh = False .TextFilePlatform = 850 .TextFileStartRow = 1 .TextFileParseType = xlDelimited .TextFileTextQualifier = xlTextQualifierDoubleQuote .TextFileConsecutiveDelimiter = False .TextFileTabDelimiter = False .TextFileSemicolonDelimiter = False .TextFileCommaDelimiter = True .TextFileSpaceDelimiter = False .TextFileColumnDataTypes = Array(1) .TextFileTrailingMinusNumbers = True .Refresh BackgroundQuery:=False End With End Sub +-+-+-+-+-+-+-+-+-+-+-+-+ The Array(1) bit is different to what you posted (you have two parameters)? Does that make any sense? 2. Do you really get the file opened perfectly? I would guess that ASCII 13 would take the data to next row... Yes, you can replace it with a space character... ASCII 10 is treated as End of Record while importing a text file... The ASCII 13 imports as a small 'square' character (when I described that it opened perfectly). I guess I was overstating it a bit, but the 'square' rather than a line break (CR) within a cell is not an issue for me, so it is relatively perfect ;-) If I was going to use VBA to edit the file prior to importing, how would I do that though? Thanks for your assistance, -- Alan. The views expressed are my own, and not those of my employer or anyone else associated with me. My current valid email address is: This is valid as is. It is not munged, or altered at all. It will be valid for AT LEAST one month from the date of this post. If you are trying to contact me after that time, it MAY still be valid, but may also have been deactivated due to spam. If so, and you want to contact me by email, try searching for a more recent post by me to find my current email address. The following is a (probably!) totally unique and meaningless string of characters that you can use to find posts by me in a search engine: ewygchvboocno43vb674b6nq46tvb |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Importing CSV - Avoiding conversion to 'dates', but totally different results when controlling manually | Excel Discussion (Misc queries) | |||
Infopath w/ manually entered values in drop-down and qry results | Charts and Charting in Excel | |||
importing a jpg causes conversion dialog box to open | Excel Discussion (Misc queries) | |||
Avoiding "0" importing blank cells from another Excel file | Excel Discussion (Misc queries) | |||
Totally Lost - Dates in huge table, Range finder | Excel Discussion (Misc queries) |