I need a different method to import data into excel from txt

[foozball3000]

Our current method has a 50% fail ratio. And that doesn't work, because at both demos it failed. Embarrassing.

Currently, we're using this function in VBA to import a specific part of the dataset.

With ActiveSheet.QueryTables.Add( _
        Connection:="ODBC;DBQ=Data;DefaultDir=;Driver={Microsoft Text Driver (*.txt; *.csv)};", _
        Destination:=ActiveSheet.Range("A1"))
        .CommandText = "SELECT * FROM TestDataset.txt WHERE [UserID] = '" & UserID & "'"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
        On Error Resume Next
    End With

Is there another sensible way?

 

[gifs]

Manually copy and paste the data from the text file into individual cells.

 

[Necuno]

import from excel and select correct things onetime ?

 

[foozball3000]

All of it must be automated guys. That's the whole point of VBA. But I'm poking around on an idea that might actually work.

 

[SirFooK'nG]

MS Access?? Used to do a ton of text imports from VIP payroll into Access. From there pretty much do what you like with it. Didnt matter about the format either, tab delimited, CSV ... whatever...

 

[greggpb]

use the msquery object in excel ?

Data->External Data->Database Query

 

[foozball3000]

^%$#@#$%^&*(!!!
I HATE VBA! I HATE EXCEL!!! Nothing works!

 

[greggpb]

just manipulate the data in C# or vb and output a csv file with the results..

then use a linked spread sheet to use the csv as a datasource

 

[Bukabi]

If your targeted version is Excel 2007, then you can create the .xlsx file “on the fly” using C#, VB.NET etc. without even having Excel installed on the server/client. It becomes a simple XML manipulation.

 

[diabolus]

I would look into SSIS [SQL Server Integration Services] . Even though it's based around SQL Server, the whole point of it is to move around data between different databases/formats...especially Excel,CSV,XML etc.


Anyhow, what we do with this sort of thing is
a] Get the source data into a database [SQL typically] -> basically importing the .txt file into a table. [SSIS or DTS works great here]
b] Clean it up [SQL stuff]
c] Export it using whatever [Excel has built in functionality]

If you got it into a database you can actually connect from excel directly [Data -> Import Data ] ......

 

[guest2013-1]

What I find funny is that everyone wants everything done in excel.

What is the purpose of this Fooz?

 

[foozball3000]

What I find funny is that everyone wants everything done in excel.

What is the purpose of this Fooz?

It's a WorkBook that we give to clients, and to keep the size down, we run all the data from a text file. If it wasn't for Excel's auto column formatting, it would have worked great. We decided on the same route as before, but just changing the Index Column. It should work...

 

[DarrylH]

do everything you want done once, just make sure you are recording a new macro before you start and then do everything in order that you want it done. Format it, move it,do what eva you want with it, and when you are complete stop the macro and modify as you see fit.

 

[koeks]

I would look into SSIS [SQL Server Integration Services] . Even though it's based around SQL Server, the whole point of it is to move around data between different databases/formats...especially Excel,CSV,XML etc.


Anyhow, what we do with this sort of thing is
a] Get the source data into a database [SQL typically] -> basically importing the .txt file into a table. [SSIS or DTS works great here]
b] Clean it up [SQL stuff]
c] Export it using whatever [Excel has built in functionality]

If you got it into a database you can actually connect from excel directly [Data -> Import Data ] ......

plus 1 on that one... i use DTS(ms sql server 2000) works like a charm...

 

[Nod]

Import data into a MySQL table.
Use QueryBrowser (GUI Tools) to query the database.
Save results as excel file.