Issue while importing CSV int EXCEL
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
05-04-17 12:10 PM
Hi Team,
This is regarding an issue while importing a csv into excel.
I am facing an issue in importing csv to excel via excel VBO. I did this successfully. But the problem is the imported csv datas are not in text format. This means they loose trailing zeros of the numbers. For example : If a number is like 0000001234 and while importing it to excel the number lost its leading zeros and imported like 1234. This makes a series issue in my project and am not able to go forward with this project.
Can you kindly please help me to solve this issue as soon as possible.
Thank you
4 REPLIES 4
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
05-04-17 02:57 PM
This seems like a possible solution for you: https://answers.microsoft.com/en-us/msoffice/forum/msoffice_excel-mso_o
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
06-04-17 12:13 PM
Hi Sebastien,
Thanks for your consideration. Actually the answer is not sufficient for me. My problem is when I am importing a csv file into excel using EXCEL VBO, (I mean Import CSV VBO) the data will load into the excel but not in the text format. so that the numbers lost its trailing zeros (Generally said the text is converted into General format). What should I do to rectify this and tell me whether I need to change the code in IMPORT CSV VBO. I hope you understand the issue. Kindly refer the example I wrote above.
Kindly help me as this is a critical issue for me
Thank you
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
21-09-17 06:25 PM
Hello,
I was wondering how did you get the import CSV to work? I am not sure what to put in the Range Field and the Qualifier Code. I have tried to put a random range like ""A1:R50"" and I am getting an Invalid Index Exception still.
Thanks
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content
09-03-18 08:15 PM
Hi, I created another Import CSV object with the below code. See the "" 'added"" lines. In this case I'm passing in the ColumnDelimiter and ColumnArray text variables. My delimiter is ""~"" in this case, but you can pass in any delimiter you need (such as "","" etc.). The ColumnArray variable contains the column format numbers (ex. ""1,2,1"" where 1=general and 2=text), so column 1 would be general, column 2 would be text, column 3 general.
Dim dw, ds, dr, qt, ArrayText() As Object 'added ArrayText()
Dim i As Integer
Try
dw = GetWorkbook(Handle, Workbook)
ds = GetWorksheet(Handle, Workbook, Worksheet)
dr = ds.Range(Range)
qt = ds.QueryTables.Add(Connection:=""TEXT;"" & Path, Destination:=dr)
ArrayText = Split(ColumnArray,"","") 'added line
Dim ArrayNum(ArrayText.Length-1) as Integer 'added line
For i=0 to ArrayText.Length-1 'added line
ArrayNum(i) = ArrayText(i) * 1 'added line
Next 'added line
With qt
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = 1 'xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = startrow
.TextFileParseType = 1 'xlDelimited
.TextFileTextQualifier = Qualifier
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = ColumnDelimiter 'added ColumnDelimiter variable
.TextFileColumnDataTypes = ArrayNum 'added ArrayNum variable
.TextFileTrailingMinusNumbers = True
End With
qt.Refresh(False)
Success = True
Catch e As Exception
Success = False
Message = e.Message
Finally
dw = Nothing
ds = Nothing
dr = Nothing
qt = Nothing
End Try
