-
Feb 18th, 2019, 07:17 PM
#1
Thread Starter
Junior Member
Error importing text file to Excel Sheets
Hello, I am working on a project which takes a > 1M line tab delimited text file to several Excel worksheets.
I got the file to work somewhat: you see, the file has some empty fields between each other and my project treats it as one tab. This, in return, makes the fields go to different columns. I noticed this was due to the .TextFileConsecutiveDelimiter = True. However, when I comment out this line and try to export the file again I get an error telling me there's too much data on the first sheet. This is far before all files are imported to the Excel file. The only way I can fix this is to uncomment the aforementioned line, but I am back to having the columns in the wrong places.
I am stuck, can someone please help me? Thank you so much!
PD: the files needed are located on a folder in the project itself. They're created when the user imports the necessary large file.
Code:
Private Sub createExcel(pagina)
encabezados.Clear()
Dim listaEncabezados = sacarEncabezadosCargaAmazon()
If Not listaEncabezados.EOF Then
While Not listaEncabezados.EOF
For i = 1 To listaEncabezados.Fields.Count - 1
encabezados.Add(listaEncabezados.Fields(i).Name)
Next
listaEncabezados.MoveNext()
End While
End If
Dim hoja = 1
Dim excel As Excel.Application = New Microsoft.Office.Interop.Excel.Application()
If excel Is Nothing Then
MessageBox.Show("Error. Excel 2016 no está instalado en esta computadora", "Error")
Exit Sub
End If
Dim iSheetsPerBook As Integer
Dim misValue As Object = System.Reflection.Missing.Value
Dim xlWorkBook As Excel.Workbook
Dim xlWorkSheet As Excel.Worksheet
excel.SheetsInNewWorkbook = pagina
For i = 1 To pagina
iSheetsPerBook = excel.SheetsInNewWorkbook
xlWorkBook = excel.Workbooks.Add(misValue)
excel.SheetsInNewWorkbook = iSheetsPerBook
Next
For x = 1 To pagina
Dim archivo = String.Format("{0}\ArchivosExcel\excel{1}.txt", dondeEstaElArchivo, x)
xlWorkSheet = xlWorkBook.Worksheets(hoja)
Dim qt As QueryTable = xlWorkSheet.QueryTables.Add(Connection:=String.Format("TEXT;{0}", archivo), Destination:=xlWorkSheet.Range("$A$2"))
With qt
.Name = "Import" & hoja
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = XlCellInsertionMode.xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 1252
.TextFileStartRow = 1
.TextFileParseType = XlTextParsingType.xlDelimited
.TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = GetColumnDataTypes(xlWorkSheet.Columns.Count)
.TextFileTrailingMinusNumbers = True
.BackgroundQuery = True
.Refresh()
End With
Dim columnaEncabezado = 1
For Each enc As String In encabezados
xlWorkSheet.Cells(1, columnaEncabezado) = enc
columnaEncabezado += 1
Next
hoja += 1
Next
Dim saveDialog As New SaveFileDialog()
saveDialog.Title = "Por favor elija donde quiere guardar el archivo"
saveDialog.Filter = "Excel 2007-on (*.xlsx)|*.xlsx"
Dim respuesta = saveDialog.ShowDialog()
If respuesta = DialogResult.Cancel Or saveDialog.FileName = "" Then
Exit Sub
End If
Dim fileSave = saveDialog.FileName
xlWorkBook.SaveAs(fileSave)
xlWorkBook.Close(True, misValue, misValue)
excel.Quit()
releaseObject(xlWorkSheet)
releaseObject(xlWorkBook)
releaseObject(excel)
End Sub
-
Feb 19th, 2019, 03:19 AM
#2
Re: Error importing text file to Excel Sheets
Hi
for testing setup a DataTable and put the Tabdelim. Textfile in a Datagridview first (see Button1)
I added 4 Fields in my Test file
if the Data is correct export it (see Button2)
Code:
Imports System.IO
Imports Microsoft.Office.Interop
Public Class Form1
Private Dt As New System.Data.DataTable
'read the Tab file
Private fn As String = "E:\tabDelim.txt"
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim r As System.Data.DataRow
With Dt
'4 Fields in my sample Data
.Columns.Add("F1", GetType(String))
.Columns.Add("F2", GetType(String))
.Columns.Add("F3", GetType(String))
.Columns.Add("F4", GetType(String))
Dim sr As New IO.StreamReader(fn)
Do While sr.Peek() <> -1
r = .NewRow
r.ItemArray = sr.ReadLine().Split(New Char() {vbTab(0)}, 4, System.StringSplitOptions.None)
.Rows.Add(r)
Loop
sr.Close()
'now you have a Datatable to work with
DataGridView1.DataSource = Dt
End With
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Author/Source: kpmc / DGV -CopyClipboard
DataGridView1.DataSource = Dt
DataGridView1.RowHeadersVisible = False
DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
Clipboard.Clear()
'apply any RowFilters
DataGridView1.SelectAll()
Clipboard.SetDataObject(DataGridView1.GetClipboardContent)
DataGridView1.ClearSelection()
If Clipboard.GetDataObject IsNot Nothing Then
Dim _Excel As New Excel.Application With { _
.Visible = True}
Dim _WorkBook As Excel.Workbook
_WorkBook = _Excel.Workbooks.Add()
Dim _Worksheet As Excel.Worksheet
_Worksheet = CType(_WorkBook.Worksheets(1), Excel.Worksheet)
_Worksheet.Range("A1").Select()
_Worksheet.Paste()
_Worksheet.UsedRange.Columns.ColumnWidth = 50
_Worksheet.UsedRange.Columns.AutoFit()
_Worksheet.UsedRange.Rows.AutoFit()
End If
End Sub
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Dim r As System.Data.DataRow
'With Dt
' .Columns.Add("F1", GetType(String))
' .Columns.Add("F2", GetType(String))
' .Columns.Add("F3", GetType(String))
' .Columns.Add("F4", GetType(String))
' Dim sr As New IO.StreamReader(fn)
' Do While sr.Peek() <> -1
' r = .NewRow
' r.ItemArray = sr.ReadLine().Split(New Char() {vbTab(0)}, 4, System.StringSplitOptions.None)
' .Rows.Add(r)
' Loop
' sr.Close()
' 'now you have a Datatable to work with
'' DataGridView1.DataSource = Dt
'End With
''Author/Source: kpmc / DGV -CopyClipboard
'DataGridView1.DataSource = Dt
'DataGridView1.RowHeadersVisible = False
'DataGridView1.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableAlwaysIncludeHeaderText
'Clipboard.Clear()
''apply any RowFilters
'DataGridView1.SelectAll()
'Clipboard.SetDataObject(DataGridView1.GetClipboardContent)
'DataGridView1.ClearSelection()
'If Clipboard.GetDataObject IsNot Nothing Then
' Dim _Excel As New Excel.Application With { _
' .Visible = True}
' Dim _WorkBook As Excel.Workbook
' _WorkBook = _Excel.Workbooks.Add()
' Dim _Worksheet As Excel.Worksheet
' _Worksheet = CType(_WorkBook.Worksheets(1), Excel.Worksheet)
' _Worksheet.Range("A1").Select()
' _Worksheet.Paste()
' _Worksheet.UsedRange.Columns.ColumnWidth = 50
' _Worksheet.UsedRange.Columns.AutoFit()
' _Worksheet.UsedRange.Rows.AutoFit()
'End If
End Sub
End Class
HTH
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Feb 19th, 2019, 11:05 AM
#3
Thread Starter
Junior Member
Re: Error importing text file to Excel Sheets
Hello ChrisE, thank you for your reply.
Is there a way I can use the DataTable directly without resorting to a datagridview? I am working with too much data and I don't want the application to be slower due to this.
Thank you again
-
Feb 19th, 2019, 11:05 AM
#4
Thread Starter
Junior Member
Re: Error importing text file to Excel Sheets
Hello ChrisE, thank you for your reply.
Is there a way I can use the DataTable directly without resorting to a datagridview? I am working with too much data and I don't want the application to be slower due to this.
Thank you again
-
Feb 19th, 2019, 11:31 AM
#5
Thread Starter
Junior Member
Re: Error importing text file to Excel Sheets
Hello again ChrisE, I tried your method anyway and it ran out of memory on the first try
Each file has a limit of one million lines, so that could be the reason.
-
Feb 19th, 2019, 12:09 PM
#6
Re: Error importing text file to Excel Sheets
Hi,
do you really want Excel sheets with 1 Mio. lines ?
anyway why not sort the problem first with the blank spaces.
do you know Powershell ?
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Feb 19th, 2019, 12:11 PM
#7
Thread Starter
Junior Member
Re: Error importing text file to Excel Sheets
Hello,
I need to have as much information as possible with as little sheets as possible, so I need to say yes.
And, to answer your second question, I don't know Powershell
-
Feb 19th, 2019, 12:41 PM
#8
Re: Error importing text file to Excel Sheets
why not use a Database ?
Access ,Sql-Server
you could sort and group the Data there and then Export to Excel ?
I think Access has a Import Function for Text files
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Feb 19th, 2019, 12:43 PM
#9
Thread Starter
Junior Member
Re: Error importing text file to Excel Sheets
Thank you for reminding me. I am using MySQL to write the text files. ADODB
-
Feb 19th, 2019, 05:52 PM
#10
Re: Error importing text file to Excel Sheets
If all you want to do is put the text file data into an Excel file then just open the text file in excel and use the SaveAs method on the WorkBook.
Code:
Imports Excel = Microsoft.Office.Interop.Excel
Imports Microsoft.Office
Public Class Form9
Public Sub OpenExcelDemo(ByVal FileName As String)
If IO.File.Exists(FileName) Then
Dim xlApp As Excel.Application = Nothing
Dim xlWorkBook As Excel.Workbook = Nothing
xlApp = New Excel.Application
xlApp.DisplayAlerts = False
xlWorkBook = xlApp.Workbooks.Open(FileName)
xlApp.Visible = True
'MessageBox.Show("File was opened")
xlWorkBook.SaveAs(Filename:="C:\Ajunk2015\TestSaveAs.xlsx", FileFormat:=Microsoft.Office.Interop.Excel.XlFileFormat.xlOpenXMLWorkbook)
xlApp.Quit()
ReleaseComObject(xlWorkBook)
ReleaseComObject(xlApp)
Else
MessageBox.Show("'" & FileName & "' not located. Try one of the write examples first.")
End If
End Sub
Public Sub ReleaseComObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
End Try
End Sub
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
OpenExcelDemo("C:\ajunk2015\book1.txt")
End Sub
End Class
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|