473,699 Members | 2,129 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

save a flat export data file as an excel file

Hi all,

I have a question for you.

I have a .csv file which has many lines of data.
Each line has many data fields which are delimited by ",".
Now I need to extract part of data from this file but save it as an excel
file.

The data in this excel file will be imported into an Access database. The
reason I have to save it into an excel file is Access program only accept
excel data file, but not flat text file.

Can you tell me how to save extracted data into an excel file?

Thanks.
--
Betty
Jul 17 '08 #1
3 7153
Why go to the trouble of converting CSV to Excel and then Excel to Access????

Why not just use ASP to go directly from CSV to Access???

<%
Set csvconn = Server.CreateOb ject("ADODB.Con nection")
csvconn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=c:\path\ to\DIRECTORY\on ly\;" & _
"Extended Properties=""te xt;HDR=Yes;FMT= Delimited"""

Set dbconn = Server.CreateOb ject("ADODB.Con nection")
dbconn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=c:\path\ to\yourAccessDa tabase.mdb;"

SQL = "SELECT * FROM nameOfFile.csv"
Set inRS = csvconn.Execute ( SQL )

Set outRS = Server.CreateOb ject("ADODB.Rec ordset")
outRS.Open "TableName" , dbconn, adOpenStatic, adLockPessimist ic

Do Until inRS.EOF
outRS.AddNew
outRS("field1") = inRS("fieldA")
outRS("field2") = inRS("fieldB")
... etc ...
outRS.Update
inRS.MoveNext
Loop
outRS.Close
inRS.Close
dbConn.Close
csvConn.Close
%>

THere are actually some even more efficient ways to do this, but without
know more of your details I'm hesitant to choose one. The above is simple
minded enough it should work so long as your CSV file doesn't have any quirks.
Jul 17 '08 #2
Hi Betty,

From your description, you have an existing CSV file and you want to
extract some part of the CSV file out and export them into excel file,
correct?

Are you wantting to do it programmtically or just want to do such an
transformation and not necessary to use code? Based on my experience,

#SQL Server 2005 ¨C Integration Services
http://technet.microsoft.com/en-us/s.../bb671392.aspx

#How to import an Excel file into SQL Server 2005 using Integration Services
http://www.builderau.com.au/program/...t-an-Excel-fil
e-into-SQL-Server-2005-using-Integration-Services/0,339028455,339 285948,00.h
tm

http://www.mssqltips.com/tip.asp?tip=1202

If this need to be done programmtically , a common approach is read the csv
file via OLD db provider(jet engine) and access the data like a database
table. However, I think it maybe a bit hard to export it exactly as excel
format, btw, excel can directly convert csv format, will it work for your
case.

#How To Open Delimited Text Files Using the Jet Provider's Text IIsam
http://support.microsoft.com/default...microsoft.com:
80/support/kb/articles/Q262/5/37.ASP&NoWebCon tent=1&NoWebCon tent=1

#Using OleDb to import text files (tab, csv, custom)
http://www.codeproject.com/KB/cs/UsingJetForImport.aspx

Also, are you going to do this converting in ASP web page? If not, for
normal desktop applicatin, we can use excel automation object model to
create excel worksheet:

http://support.microsoft.com/kb/302096

http://support.microsoft.com/kb/302094

Sincerely,

Steven Cheng

Microsoft MSDN Online Support Lead
Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
ms****@microsof t.com.

=============== =============== =============== =====
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
=============== =============== =============== =====
This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
>From: =?Utf-8?B?YzY3NjIyOA= =?= <be***@newsgrou p.nospam>
Subject: save a flat export data file as an excel file
Date: Wed, 16 Jul 2008 20:24:01 -0700
>
Hi all,

I have a question for you.

I have a .csv file which has many lines of data.
Each line has many data fields which are delimited by ",".
Now I need to extract part of data from this file but save it as an excel
file.

The data in this excel file will be imported into an Access database. The
reason I have to save it into an excel file is Access program only accept
excel data file, but not flat text file.

Can you tell me how to save extracted data into an excel file?

Thanks.
--
Betty
Jul 17 '08 #3

"Old Pedant" <Ol*******@disc ussions.microso ft.comwrote in message
news:90******** *************** ***********@mic rosoft.com...
Why go to the trouble of converting CSV to Excel and then Excel to
Access????

Why not just use ASP to go directly from CSV to Access???

<%
Set csvconn = Server.CreateOb ject("ADODB.Con nection")
csvconn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=c:\path\ to\DIRECTORY\on ly\;" & _
"Extended Properties=""te xt;HDR=Yes;FMT= Delimited"""

Set dbconn = Server.CreateOb ject("ADODB.Con nection")
dbconn.Open "Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=c:\path\ to\yourAccessDa tabase.mdb;"

SQL = "SELECT * FROM nameOfFile.csv"
Set inRS = csvconn.Execute ( SQL )

Set outRS = Server.CreateOb ject("ADODB.Rec ordset")
outRS.Open "TableName" , dbconn, adOpenStatic, adLockPessimist ic

Do Until inRS.EOF
outRS.AddNew
outRS("field1") = inRS("fieldA")
outRS("field2") = inRS("fieldB")
... etc ...
outRS.Update
inRS.MoveNext
Loop
outRS.Close
inRS.Close
dbConn.Close
csvConn.Close
%>

THere are actually some even more efficient ways to do this, but without
know more of your details I'm hesitant to choose one. The above is simple
minded enough it should work so long as your CSV file doesn't have any
quirks.
Yep. Much more efficient is to simpy set up a connection to the Access
database, and then use a SQL statement like this:

INSERT INTO AccessTable (Field1, Field2, ...Fieldn) SELECT Col1, Col2,
....Coln FROM [Text;DATABASE=" & path_to_file & ";].[myfile.csv]

More info here: http://www.mikesdotnetting.com/Artic...x?ArticleID=67
(it's .NET - but the SQL etc is identical for classic ASP).

--
Mike Brind
MVP - ASP/ASP.NET
Jul 17 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3474
by: Aliza Klein | last post by:
I am new to ASP so please pardon me if this is crazy. I have data that I want to allow the user to be able to save in a format that will then enable the user to open the data in Excel. (I have already read the previous CSV posts and still have a question.) I have an ASP page that takes some data from a previous page,adds some column headings and displays it all in apparent CSV format. All I am trying to do is allow the users to do a...
2
2977
by: Kit Truong | last post by:
Hello, I have an asp page that produces output from a database. This allows a simple way for the user to save the data to a text file by going to File->Save as... The default save as options always defaults to a particular filename and the save as type is always html, therefore the user has to manually type a filename and choose Text File (*.txt) as the type.
1
5029
by: Matt | last post by:
I have an ASP page that calls ASP routines that I created that execute a database query and return the results to a recordset. I then iterate through the recordset and display the data in a table. Before I iterate through the recordset I instruct the browser that the content type is Excel using the following line: (Response.ContentType = "application/vnd.ms-excel") This works fine with Excel 2003 but with older versions (I tested Excel...
4
2207
by: amywolfie | last post by:
I have a curious mission: I converted an Excel file to a relational Access 2002 database, and now I have to export data back to IT as a FLAT .csv file. I know how to create many records from one, but going back to one from many is hurting my brain. I have 4 tables: tblFees, tlkpCalcMethods, tjnFees_Calcs, and tlkpCalcData
4
4530
by: Jae | last post by:
I'm writing a web application that exports and imports excel files. The application gets a list of users and their info and displays it in a datagrid .The user then selects to save the file as a tab delimited file or an excel file. The application then saves the file in the correct format. The flip side is for the user to import/upload the file to the server The application must be able to import the excel file and read the contents. I...
1
5542
by: new | last post by:
I have data for each week in a single table. I need to export this data to a separate flat file for each week. Any ideas? DB2 SQL Query export to flat files as a function of data on each record
2
10985
by: vbaDev | last post by:
Hi. I am using Access 2000 and in my code I'm exporting a table into an Excel file (creating it), then the code needs to export another query into the same file (a new worksheet). So I needed both a "Save As" dialog and the ability to grab the filepath so that the second export appends to it. Anyway, I found Microsofts method and it works, except that I can't figure out how to populate the File Name box in the Dialog with a default name (say,...
1
9775
by: smaczylo | last post by:
Hello, I've recently been asked to work with Microsoft Access, and while I feel quite comfortable with Excel, I'm at a complete loss with databases. If someone could help me with this issue I'm having I'd be most appreciative. The database is already constructed, I'm just wanting to export the data to an excel file. In short, I'm hoping to export two Tables (or queries...not sure which to use - they both seem to have the same data) in...
3
4353
by: evenlater | last post by:
I have an Access application on a terminal server. Sometimes my users need to export reports to pdf, rtf or xls files and save them to their own client device hard drives. They can do that right now the way I have this set up, but it's confusing and slow. When they browse for a place to save the reports, they see all of the drives on the terminal server as well as their own client drives. So they're likely to want to choose "My...
0
8621
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9182
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9041
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8928
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6538
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5877
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4379
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4634
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2013
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.