Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
fermier
Starting Member
5 Posts |
 Posted - 2004-11-08 : 08:03:11
|
| Dear all,In trying to export "real" text fixed format data from MS Access 2000, I use the format function, Format([field]; format type), on some fields in a make table query. This works reasonably well, except that the resulting fields from the Format function in the new table has text fields of 255 characters wide. I need only 12 characters. When I try to limit this using Left(Format([field]; field format); 12) I still get fields that are 255 characters wide. If I change the approach to first create the desired table with certain fields of 12 characters wide and then use the above-mentioned query to append data to this table I get zero fields appended as there is of course a type missmatch between the fields that are 12 characters wide and the non-appended 255 character-wide fields.Could anyone kindly provide me with some pointers as to how I can avoid this problem?Thanks in advance.PS. Example query:SELECT Left(Format([d_adm_ntc],"Short Date"),12) AS dadm_ntc, Left(Format([erp_h_dbw],"0.0"),12) AS erph_dbw, Left(Format([erp_v_dbw],"0.0"),12) AS erpv_dbw, Left(Format([d_updated],"Short Date"),12) AS dupdated, Left(Format([conv_freq_assgn],"000.000"),12) AS cnv_freq_assgn INTO tDTDSFROM fmtv_terra;PPS. I hope I did not create any syntax errors in the example as it is only a small part of the real query. Any syntax errors that may have resulted in my trying to produce this shorter version should please be ignored as it is not part of the problem. |
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-11-08 : 15:06:10
|
| Did you try importing from MSSQL instead of exporting from MSAccess?*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
 |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-08 : 15:06:26
|
| Why are you creating a table in MS Access? I thought you were exporting data FROM access -- why do you need to create a table?- Jeff |
|
|
|
fermier
Starting Member
5 Posts |
Posted - 2004-11-09 : 07:15:58
|
quote:
Originally posted by tuenty
Did you try importing from MSSQL instead of exporting from MSAccess?*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle
At the moment the data is only available in MSAccess. |
|
|
|
fermier
Starting Member
5 Posts |
Posted - 2004-11-09 : 07:38:01
|
quote:
Originally posted by jsmith8858
Why are you creating a table in MS Access? I thought you were exporting data FROM access -- why do you need to create a table?- Jeff
You are correct Jeff to ask this question. I tried the direct text export from MS Access as fixed text. This created several problems. MS Access throws away the significant figures after the second decimal for floating point values and the format is not fixed, it is variable within a fixed column. Both of these are problems to the software that will be using the data. When I run a query that formats the data in the desired manner, I can create a table of this data. If I then export the data from this table in fixed text format, then the format is really fixed and correct, plus, as the numerical and date fields are now considered as text, MS Access no longer performs any unwanted processing on them before exporting them to the text file. I have not discovered a way in which I can export the data in really fixed text format directly from the query.In short, the direct ways did not work for me and I had to discover work arounds. I have now only this one remaining problem of the text fields of 255 characters that MS Access generates, which I would like to stop from happening.fermierPS. I hope this group is not the wrong group for posting my question. If it is I apologise! |
|
|
|
tuenty
Constraint Violating Yak Guru
278 Posts |
Posted - 2004-11-09 : 07:56:07
|
quote:
Originally posted by fermier
quote:
Originally posted by tuenty
Did you try importing from MSSQL instead of exporting from MSAccess?*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle
At the moment the data is only available in MSAccess.
Yes, I got that. What I'm saying is that you can export from Access ==> to SQL oryou can IMPORT to SQL <== from Access using EM\tools\Wizards...\Data transformation ServicesDTS Import Wizard.The wizard will ask for the provider (access) and the file location of the source and destination, you can import a whole table or the result of a query and you can make transformation to the destinationtable.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*A candle loses nothing by lighting another candle |
|
|
|
fermier
Starting Member
5 Posts |
Posted - 2004-11-09 : 10:45:47
|
| [/quote]Yes, I got that. What I'm saying is that you can export from Access ==> to SQL oryou can IMPORT to SQL <== from Access using EM\tools\Wizards...\Data transformation ServicesDTS Import Wizard.The wizard will ask for the provider (access) and the file location of the source and destination, you can import a whole table or the result of a query and you can make transformation to the destinationtable.[/quote]Ah, now I understand your comment. No, I do not have MS SQL available and even if I did this will not help, because in the end the users should be able to do this on their own PC's and they certainly don't all have MS SQL and the end users are all over the world. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-11-09 : 13:05:48
|
are you familiar with VB? you are probably better off writing this routines in VB -- I also have never been happy with the way MS Access outputs text files. not enough options.for example, in vb, you can really control the output: (you need to add a reference to the DAO object library for this code to work)dim r as dao.recordsetset r = currentdb.openrecordset("YourQueryToExport")open "C:\output.txt" for output as #1do while not r.eof ' here you print each column exactly as you need: ' column 1 with be 100 characters, left aligned, padded with spaces: print #1, Left(r!Column1 & space(100),100); ' column 2 is 2 decimal places, fixed width of 20 characters, padded with spaces: print #1, left(format(r!column2, "#.00") & space(20),20); ' column 3 might be null; replace that with <NULL>, padd to 30 characters: print #1, left(nz(r!Column2,"<NULL>") & space(30),30); ' and so on ... do as you need ' end the line like this: print #1, "" r.movenextloopclose #1r.closeyou can write generic routines like this as needed and you can be very specific about how to format all files. you can print a header line first if you like and have full control over formatting. and you keep your query as is -- no converting datetimes to strings or things like that, just return the data in the query (no formatting) and use VB to handle all the formatting.- Jeff |
|
|
|
fermier
Starting Member
5 Posts |
Posted - 2004-11-17 : 11:16:56
|
quote:
Originally posted by jsmith8858
are you familiar with VB? you are probably better off writing this routines in VB -- I also have never been happy with the way MS Access outputs text files. not enough options.- Jeff
Well, I am not too familiar with VB, just enough to get myself into trouble (I'm more of a C and a FORTRAN person), but thanks for the advice. I was afraid that it would eventually come to this and I am sure you are right. Now I need to find a way to do this so that these users would be able to use the export routines without having to install too many MSVB libraries. Thanks again for the advice and the example. It will come in handy. It is a pity that there are problems with the text export of MS Access, or else it would have been very useful.Thanks again! |
|
|
|
|
|
|
|
|
Fixed format from MS Access