Import Access DB with notes that stay on different columns by typology.

The DB is an Access file, where customers are on a table, and notes are on another, where every single appointment has his notes.

Every single note is inside a column based on typology like this:

ServizioSalone	Colore	Meches	Permanenti
SH 1.4 + SCHIUMA	7 + 6/2 oss 20 vol OSS. 30 VOL	10/81 FRESH FATTO ANTO
SHAMPOO SILVER +MOUSSE		DEC. LIGHT SYSTEM OSS. 40 VOL	LOCKWELL

So here are steps to import this type of notes:

1. Export from Access Customers table and Notes table

From Access, export the two tables in xls file, and then convert it to csv.

2. Import to mysql

Create a temporary DB in a mysql and import the two csv files in two different tables:

customers_table
notes_table

3. Concat notes in a single column "note" for every appointment row

UPDATE note_table SET note = TRIM(CONCAT(
if(CuteCapello IS NULL OR LENGTH(CuteCapello)=0,"", CONCAT("CuteCapello: ", CuteCapello, '\n')),
if(ServizioSalone IS NULL OR LENGTH(ServizioSalone)=0,"", CONCAT("ServizioSalone: ", ServizioSalone, '\n')),
if(Colore IS NULL OR LENGTH(Colore)=0,"", CONCAT("Colore: ", Colore, '\n')),
if(Meches IS NULL OR LENGTH(Meches)=0,"", CONCAT("Meches: ", Meches, '\n')),
if(Permanenti IS NULL OR LENGTH(Permanenti)=0,"", CONCAT("Permanenti: ", Permanenti, '\n')),
if(Exstension IS NULL OR LENGTH(Exstension)=0,"", CONCAT("Exstension: ", Exstension, '\n')),
if(Estetica IS NULL OR LENGTH(Estetica)=0,"", CONCAT("Estetica: ", Estetica, '\n')),
if(ConsiglioCurativo IS NULL OR LENGTH(ConsiglioCurativo)=0,"", CONCAT("ConsiglioCurativo: ", ConsiglioCurativo, '\n')),
if(Piega IS NULL OR LENGTH(Piega)=0,"", CONCAT("Piega: ", Piega, '\n')),
if(Prodotti IS NULL OR LENGTH(Prodotti)=0,"", CONCAT("Prodotti: ", Prodotti, '\n'))
))

4. Create a function that return notes of last 5 appointments for every customer:

DROP FUNCTION IF EXISTS get_notes;
DELIMITER $$
CREATE FUNCTION get_notes(cliente TEXT)
RETURNS TEXT
LANGUAGE SQL -- This element is optional and will be omitted from subsequent examples
BEGIN
DECLARE note_cliente VARCHAR(1000);
SET note_cliente = "";
SELECT GROUP_CONCAT(note1 SEPARATOR '\n' ) INTO note_cliente
FROM
(SELECT DISTINCT
idcliente, CONCAT(Data, ":\n", note) as note1
FROM
notes_table
WHERE idcliente = cliente
ORDER BY ID DESC
LIMIT 5) AS notes;
RETURN note_cliente;
END;
$$
DELIMITER ;

5. Insert notes in customer_table

Now you can insert in a column "note" the final concats notes of every customer:

UPDATE customers_table SET note = get_notes(idCliente)

6. Export final table

Export the final customers_table from mysql, it should contains all needed for import in backend.

Import DB "Narciso" from .bak Files

if the DB is a bak file from Narciso Software, it should be imported in Sql Server. For do it, create a DB with name "Narciso", right click and go to "Attività -> Ripristina -> Database". Select "Periferica di Origine" and point to the file .bak, than click con "Ripristina" flag. In "Opzioni" select "Sovrascrivi il database esistente".

The generic query for extract customer+notes is (keep in mind to replace carriage return when export from sql server):

SELECT [FirstName]
,[LastName]
,[Address]
,[City]
,[Prov]
,[ZipCode]
,[Tel1]
,[Cell1]
,[Mail]
,[FiscalCode]
,[PIva]
,[BirthDate]
,[AcceptMail]
,[AcceptSms]
,[Sex]
,[LastMeetDateTime]
, (SELECT MAX( REPLACE(REPLACE(N.NoteContent,CHAR(10),' '),CHAR(13),''))
FROM [Narciso].[dbo].[CustomerNotes] as N
WHERE C.id = N.idCustomer ) as Note
FROM [Narciso].[dbo].[Customers] as C

For export the result right click on a cell, "Salva Risultati con Nome" and save the csv file.
The csv file doesn't contain first header row with columns name, so you need to manually put it.
Replace then all "NULL" occurrencies with blank.

Import DB X Touch from .bak Files

if the DB is a bak file from X Touch Software, it should be imported in Sql Server 2008. For do it, create a DB with name "xtouch", right click and go to "Attività -> Ripristina -> Database". Select "Periferica di Origine" and point to the file .bak, than click con "Ripristina" flag. In "Opzioni" select "Sovrascrivi il database esistente".

The generic query for extract customer+notes is (keep in mind to replace carriage return when export from sql server):

SELECT [ID]
,[Anagrafica]
,[Sesso]
,[Telefono]
,[Cellulare]
,[Email]
,[DataNascita]
,REPLACE(REPLACE([Note],CHAR(10),' '),CHAR(13),'')
,[UltimoPassaggio]
,[DataRegistrazione]
FROM [narciso].[dbo].[TabClienti]

Import DataStat DB

The generic query for extract customer+notes is:

SELECT LTRIM(RTRIM(A.Nome)) Nome
,LTRIM(RTRIM(A.Indirizzo)) Indirizzo
,LTRIM(RTRIM(A.Comune)) Comune
,LTRIM(RTRIM(A.Cap)) Cap
,LTRIM(RTRIM(A.Provincia)) Provincia
,LTRIM(RTRIM(A.Sesso)) Sesso
,CONVERT(VARCHAR(10),A.DataNascita,105) DataNascita
,LTRIM(RTRIM(A.Telefono1)) Telefono1
,LTRIM(RTRIM(A.Telefono2)) Telefono2
,CONVERT(VARCHAR(10),A.DataAcquisizione,105) DataAcquisizione
,LTRIM(RTRIM(A.Email)) Email
,CONVERT(VARCHAR(10),A.DataModifica,105) DataModifica
,CONVERT(VARCHAR(10),A.UltimaPresenza,105) UltimaPresenza
,B.Note
FROM
(SELECT [Id]
,[Nome]
,[Indirizzo]
,[Comune]
,[Cap]
,[Provincia]
,[Sesso]
,[DataNascita]
,[Telefono1]
,[Telefono2]
,[DataAcquisizione]
,[Email]
,[DataModifica]
,[UltimaPresenza]

FROM [CdbNew].[dbo].[Clienti]) A LEFT JOIN (SELECT IdCliente,
STUFF((SELECT CONVERT(VARCHAR(10),DataModifica,105) + ':\n' + LTRIM(RTRIM(Caratteristica)) + '\n'
FROM [CdbNew].[dbo].[NoteClienti]
WHERE IdCliente = t.IdCliente
ORDER BY DataModifica ASC
FOR XML PATH('')),1,0,'') Note
FROM (SELECT DISTINCT IdCliente FROM [CdbNew].[dbo].[NoteClienti])t) B ON A.Id = B.IdCliente;

Import GDB DB Files

This file is for Firebird Database.
For export data you need to:

Grab free FDBConvert utility and upgrade database to the Firebird 2.5 format, it should give lots of errors, force all queries to "select * from anag" for bypass it.
Install Firebird 2.5 server. Get setup from here. Use Win32 version if your GUI tool is 32 bit application. During installation check "Generate client library as GDS32DLL for legacy app support?".
Take sure that Firebird service is running.
Install FlameRobin
Open FlameRobin, Right click on Localhost -> Register existing Database -> point to the converted db from FDBConvert, user is SYSDBA and password what used during Firebird install (usually is "masterkey")
Right click on the DB just added and select "Connect"
You should now see the ANAG tables that can be esported with Right click -> Select from -> right click on data -> Fetch all records -> then click on top left cell -> right click -> save as csv file
Open the generated csv file with a text editor, replace all tabs (\t) with ; and [null] with blank

Import STYM2 CLOUD CUSTOMERS

Use this script for export from the stym2 cloud system: https://gist.github.com/Leen15/200ee7622c95e0e33e184f0abdfff8a7
After the export, you can import customers with the generated CSV.

Import WEGEST CUSTOMERS

Use this script for export from the wegest system: https://gist.github.com/your/51f308c1ab29a0a1ba72
After the export, you can import customers with the generated CSV.

Import ALADINO CUSTOMERS

The generic query for extract customer+notes is:

SELECT [CLCODCLI]
,[CLCOGNO]
,[CLNOME]
,[CLINDIR1]
,[CLINDIR2]
,[CLNUMCAP]
,[CLPROVIN]
,[CLTELEF1]
,[CLTELEF2]
,[CLNUMCEL]
,[CL_EMAIL]
,[CLDTPRIV]
,[CLPARIVA]
,[CLCODFIS]
,[CLNOTE]
,[CLGGNASC]
,[CLMMNASC]
,[CLSESSO]
, (SELECT MAX(ACDATADO) FROM [ALAD].[dbo].[ACCETTAZ_M] WHERE ACCODCLI = [ALAD].[dbo].[CLIENTI].[CLCODCLI]) as LAST_VISIT
, (SELECT CAST((
SELECT TOP 5 CONVERT(varchar,ACDTDOCR, 103) + ': ' + LTRIM(RTRIM(ACDESCRI)) + ', ' +
LTRIM(RTRIM(CONVERT(VARCHAR,ACNOTEE))) + '§'
FROM [ALAD].[dbo].[ACCETTAZ_D]
WHERE ACSERIAL IN (SELECT ACSERIAL FROM [ALAD].[dbo].[ACCETTAZ_M] WHERE ACCODCLI = [ALAD].[dbo].[CLIENTI].[CLCODCLI])
AND CONVERT(VARCHAR,ACNOTEE) != ''
ORDER BY ACSERIAL DESC
FOR XML PATH('')) as varchar(max))) as notes
FROM [ALAD].[dbo].[CLIENTI]
ORDER BY CLCOGNO, CLNOME

For export the result right click on a cell, "Salva Risultati con Nome" and save the csv file.
The csv file couldn't contain first header row with columns name, so you need to manually put it.
Replace then all "NULL" occurrencies with blank and the char '§' with line break.

Import POLIFEMO DB

1. Export from Access Customers table and Notes table

From Access, export the two tables in xls file, and then convert it to csv.

2. Import to mysql

Create a temporary DB in a mysql and import the two csv files in two different tables:

clienti
note

3. Create a function that return notes of last 5 appointments for every customer:

DROP FUNCTION IF EXISTS get_notes;
DELIMITER $$
CREATE FUNCTION get_notes(cliente TEXT)
RETURNS TEXT
LANGUAGE SQL -- This element is optional and will be omitted from subsequent examples
BEGIN
DECLARE note_cliente VARCHAR(1000);
SET note_cliente = "";
SELECT GROUP_CONCAT(note1 SEPARATOR '\n' ) INTO note_cliente
FROM
(SELECT DISTINCT
IDCliente, CONCAT(DescrizioneServizio, ": ", NotaTecnica, "\n") as note1
FROM
note
WHERE IDCliente = cliente
AND NotaTecnica != ''
ORDER BY ID DESC
LIMIT 5) AS notes;
RETURN note_cliente;
END;
$$
DELIMITER ;

4. Insert notes in customer_table

Now you can insert in a column "note" the final concats notes of every customer:

ALTER TABLE clienti ADD COLUMN note TEXT;
UPDATE clienti SET note = get_notes(IDCliente);

6. Export final table

Export the final clienti from mysql, it should contains all needed for import in backend.

1. Export from Access Customers table and Notes table​

2. Import to mysql​

3. Concat notes in a single column "note" for every appointment row​

4. Create a function that return notes of last 5 appointments for every customer:​

5. Insert notes in customer_table​

6. Export final table​

Import DB "Narciso" from .bak Files

Import DB X Touch from .bak Files

Import DataStat DB

Import GDB DB Files

Import STYM2 CLOUD CUSTOMERS

Import WEGEST CUSTOMERS

Import ALADINO CUSTOMERS

Import POLIFEMO DB

1. Export from Access Customers table and Notes table​

2. Import to mysql​

3. Create a function that return notes of last 5 appointments for every customer:​

4. Insert notes in customer_table​

6. Export final table​

1. Export from Access Customers table and Notes table

2. Import to mysql

3. Concat notes in a single column "note" for every appointment row

4. Create a function that return notes of last 5 appointments for every customer:

5. Insert notes in customer_table

6. Export final table

1. Export from Access Customers table and Notes table

2. Import to mysql

3. Create a function that return notes of last 5 appointments for every customer:

4. Insert notes in customer_table

6. Export final table