11/22/2024 9:55:45 AM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss general SalesLogix import and data migration topics. View the code of conduct for posting guidelines.
|
|
|
|
(Probably) Simple Export & Import
Posted: 21 Dec 08 5:26 AM
|
...if there is such a thing!
Okay, probably best to give some background: We have a fairly simple SLX system in place, we sell computers, so under an account we may have a number of computers, and for each computer we have the list of components. this was developed in the past and was done by having a table under Accounts called Account_PC. We also created legacy forms to list and display this information. Also we never used the existing 'Products' or 'Contracts' sections of SLX.
We have now decided to revamp the whole thing, with New forms, and the data in new (more structured)tables. In doing so we want each computer to be a product, and the warranty or support contract that it falls under to be a contract (sounds obvious doesn't it!).
The development of the forms has been done, and the new forms look to the ACCOUNT.ACCOUNTPRODUCT table for their data, additional fields have been created there as neccesary. This has all been done in a development copy of the database.
Now to the issue: What I need to do now is drop the contents of the Products table in the Live system (it wasn't used, but people have 'experimented' ), then export the contents of the ACCOUNT.ACCOUNT_PC table (preferably to csv) so that I can re-organise for the way we are presenting the data in the new tables (some fields are removed, others are added), then import the data from the completed csv file into the ACCOUNT.ACCOUNTPRODUCT table.
In essance the process is simple; export, modify, import; but I do not know how to export the whole table(including ACCOUNTID etc), and have never used the Import Wizard (Architect Help only talks about Importing Picklists!). SO I need step-by-step instructions if possible
I have looked through previous threads, but to be honest the issues others are having seem to be more complex than just "how do I export to csv" and "how to use the Import Wizard", It might be something I can make into an article for the main site to help others new to the concept!
Thanks in Advance!
Paul. |
|
|
|
Re: (Probably) Simple Export & Import
Posted: 21 Dec 08 11:08 PM
|
Hi Paul
There are lots of ways of exporting an entire table to CSV ... this is just one.
Log in to SQL Server Management Studio, right click on your source database and select Tasks / Export Data.
Leave the options as defaults and select the database from which you want to export.
On the 'Destination' page, select 'Flat File Destination' and enter a file name (eg C:\Documents and Settings\[username]\Desktop\export.csv). Format should be delimited and text qualifier should be " If you want the first row of the text file to contain field names, check the 'Column names in the first data row' option [who'd have thought? ]
On the next page, select 'Copy data from one or more tables or views'
On the 'Configure flat file destination' page, select the table to export. Click on Preview to check the format
Then just follow the prompts. Select execute immediately and the export will run.
Good luck.
Phil
|
|
|
|
Re: (Probably) Simple Export & Import
Posted: 22 Dec 08 9:13 AM
|
| Depending on how your data is orginized you ay be able to create a Account Group with the columns you want to export and use the export to excel command from groups. |
|
|
|
Re: (Probably) Simple Export & Import
Posted: 27 Dec 08 9:48 AM
|
Phil,
(belated) Thanks for the help, it worked exactly as you said. I have done everything so far in Architect, and never thought to look for the obvious answer in SQL Management!!
Needless to say I now have the data exported (I did to Excel as it was easier to work with). I have chopped, changed and re-mapped what I want where, and now have a worksheet with the data exactly as I want it laid out in the Table.
So I thought i woluld take the 'obvious' approach, and tried to import the data using SQL manment studio, but it failed. Firstly there were errors converting unicode to non unicode in a memo field, so I removed that column (we can add it later manually, there are not that many!), but then the stopper: It reported that ACCOUNTPRODUCTID could not be Null, but in the mapping sections the field was not even listed.
Then I stopped and thought it would be best to ask what I should be doing before I make a mess of things...
So, I am now wanting to import, and would appreciate the same step-by-step assistance if possible!!
SHould I be using the Import option on SQL Management, or should I be using something in SLX?
Thanks,
Paul. |
|
|
|
Re: (Probably) Simple Export & Import
Posted: 27 Dec 08 7:38 PM
|
Hi Paul
We can make this easy, based on the assumptions that:
a) This is a one-off import
b) The data does not need to sync to remotes
If either of the above is untrue, we need to choose a more complicated way, but ...
AccountProductID - just add a new column to your source data and create the ID there. I suggest you use the following format:
QZZZZnnnnnnn
where nnnnnnn is an incrementing number, zero-filled to seven characters. Your IDs will look like this:
QZZZZ0000001
QZZZZ0000002
........
QZZZZ0000010
QZZZZ0000011
........
QZZZZ0000100
QZZZZ0000101
etc etc
Once you have built these into your spreadsheet (so that every row has a unique 12-character AccountProductID), I suggest that you save your spreadsheet as a CSV file (1st row column headings). CSV files are easier to work with when importing, I have learned through experience.
Then use SQL Server Management Studio to do the import directly, mapping the additional AccountProductID column, and all should be well.
As some background, the ID fields for SLX tables are assigned by the SLX OLEDB Provider in normal use. If you want 'proper' SLX IDs, you have to connect to the database using the provider and do your inserts there (I use either the SLX Import Wizard or SSIS to do this). The provider also creates synchronisation logs - so bypassing the provider (by connecting to the database using the SQL Native Client, for example) also bypasses synchronisation.
Good luck
Phil
|
|
|
|
Re: (Probably) Simple Export & Import
Posted: 28 Dec 08 8:57 AM
|
Phil,
Again thanks, that has worked. I now have issues over fields with quotes in them and notes fields, but I am sure I will overcome these one way or another!!
For some reason the AccountProductID field did not show up when I did this in Excel, but with a CSV it does.
I have an odd issue, don't know if it is related to the export / inport or if it is something else, there are three date fields which seem to have 'lost a day'! it is probably something I have done, and more than likely the easiest way to fix it is to change the data in excel to match, butif there is a known issue...?
Again, thanks for all your help.
Paul. |
|
|
|
Re: (Probably) Simple Export & Import
Posted: 28 Dec 08 9:31 PM
|
Hi Paul
Re fields with quotes: one way to resolve this is to replace ' with some character you do not use in your notes - eg | - then perform the import - then replace | with ' again in the final data.
Or, in your source, you can replace all single quotes ' with '''' (I think that four repeated quotes works, but it might be three - needs to be tested) and then try importing - but my first version is easier
With the date fields - are you viewing the date via SLX or via Management Studio? The first thing to look for is time-zone anomalies - you probably know that SLX holds datetime data in UTC format and performs time-zone conversions on the fly when reading and writing such data.
So ... first check the dates in Management Studio. If the date anomaly is still present, we have something weird going on and I would need some more detailed info before I could help more.
Phil
|
|
|
|
Re: (Probably) Simple Export & Import
Posted: 18 Jan 09 8:15 AM
|
Phil,
You're going to think i'm taking the preverbial, but I HAVE been working on this, I've just had a lot of other things to work on too!!
I came across some other issues, and have worked out one or two hang-ups, but have now got all of the data imported into the tables, and am nearly ready to pass the demo to management for approval....
There is just one thing:
The idea of replacing quotes with another character was used, and then there were numbers which were messed up during the import, so I used another symbol to stop that happening, and the line feeds in the notes which messed up the whole import, which aI have replaced with (yet) another character..
So now aI have all the data I need in the database, but it has ¦ ^ and ~ scattered throughout it!
You mention "then replace | with ' again in the final data". and this is what I now need to do for the various symbols, but I am (yet again) stuck as to how to do this!!
I am using SQL Server Manager and cannot find a find & replace that will search the data in the tables.
I promise, this should be it Once I get this done I (really REALLY) hope it gets passed by management and we can implement it - and I promise I will implement any help you can provide within days rather than weeks as it has been
Regards,
Paul. |
|
|
|
Re: (Probably) Simple Export & Import
Posted: 18 Jan 09 10:00 PM
|
Hi Paul
Log in to SQL Server Management Studio (as sysdba) and run an UPDATE query - here's an example:
update account
set account = replace(account, 's', ''''), account_uc = replace(account, 'S', '''')
The above code will replace all 's' characters with a single quote - in the account name field. Do not run it!!
As you can see, you can update multiple fields in the same table with a single UPDATE statement, so you will probably need one UPDATE per table.
Phil |
|
|
|
Re: (Probably) Simple Export & Import
Posted: 20 Jan 09 5:27 AM
|
Phil,
Took a bit of fiddling, but got it working (apparently some of the characters I used were not picked up in a find & replace! - ~).
The only problem I have is that there were commas and quoteation marks in the NOTES field, and apparently the replace command does not work on text fields, any ideas?
Thanks,
Paul. |
|
|
|
Re: (Probably) Simple Export & Import
Posted: 21 Jan 09 3:10 AM
|
Hi Paul, I feel like we've been on a journey together!
REPLACE does not work on TEXT fields, as you have found. As long as you are sure that none of your TEXT fields contains more than 8,000 characters, you can do it this way:
update history
set longnotes = replace(cast(longnotes as varchar(8000)), 'Phil', 'Sir Phil')
Which, in rather grand fashion, knights me, as far as SLX history is concerned.
If they are > 8,000 characters, we will have to get into some really tasty SQL coding.
Phil |
|
|
|
Re: (Probably) Simple Export & Import
Posted: 21 Jan 09 7:52 AM
|
Phil,
Does you head hut knowing so much?
You are a star, thank you very much for your help with this, it has been a real trial, but the end is in sight now.
I'll be glad when this all gets approved and installed, unfortunately then it will be on to the next project!!
Again, thanks for all your hep (to date!)
Regarsds,
Paul. |
|
|
|
Re: (Probably) Simple Export & Import
Posted: 21 Jan 09 7:35 PM
|
"Does you head hut"? Is there a problem with the 'r' on your keyboard, by any chance
It's replies like this that make it all worthwhile, thanks for the feedback and glad that you are nearing the end of your project.
Phil |
|
|
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|